Query to find filled rows only

[Zishan Ali]

Dear All,
I have a following situation:

table1

col1           col2
1                 50
2                 70
3                 null
4                 null
null              8
null               9
null               10
5                  11
6                  12
7                  14

I want to write query that count rows which are not null, i have more than 100 colums so dont want to use select count(col1),count(col2) and so on.




Replace EMP with your table name. The result will be a colum containing complete select statement with count function for all columns.


Query:
SELECT 'SELECT ' || LTRIM(MAX(SYS_CONNECT_BY_PATH(COLS, ','))
                          KEEP(DENSE_RANK LAST ORDER BY NEW1),
                          ',') || ' FROM EMP' AS STRING
  FROM (SELECT COLUMN_NAME,
               COLS,
               ROW_NUMBER() OVER(PARTITION BY COLUMN_NAME ORDER BY COLS) AS NEW1,
               ROW_NUMBER() OVER(PARTITION BY COLUMN_NAME ORDER BY COLS) - 1 AS PREV
          FROM (SELECT 1 COLUMN_NAME,
                       'COUNT(DISTINCT ' || COLUMN_NAME || ')' AS COLS
                  FROM user_tab_columns
                 WHERE TABLE_NAME = 'EMP'))
 GROUP BY COLUMN_NAME
CONNECT BY PREV = PRIOR NEW1
       AND COLUMN_NAME = PRIOR COLUMN_NAME
 START WITH NEW1 = 1;

Result:

SELECT COUNT(DISTINCT COMM),COUNT(DISTINCT DEPTNO),COUNT(DISTINCT EMPNO),COUNT(DISTINCT ENAME),COUNT(DISTINCT HIREDATE),COUNT(DISTINCT JOB),COUNT(DISTINCT MGR),COUNT(DISTINCT SAL) FROM EMP