07-22-2015, 03:42 AM
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
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