Query to find the Columns without data from a table Dynamically

Question

I wrote a Query to find the Columns without data from a table Dynamically., But its giving output only with rownum=1, which is also incorrect.,

WITH x AS (SELECT column_name FROM all_tab_cols 
WHERE OWNER='HR' AND table_name='EMPLOYEES' AND ROWNUM=1)     
SELECT X.column_name,count(X.column_name)
FROM EMPLOYEES,X
/*CONNECT BY LEVEL <= (SELECT count(1) FROM all_tab_cols 
WHERE OWNER='HR' AND table_name='EMPLOYEES')*/
group by X.column_name;

COLUMN_NAME                    COUNT(X.COLUMN_NAME)   
------------------------------ ---------------------- 
EMPLOYEE_ID                    20

when I'm trying to use level to make it dynamic it fails.,

WITH x AS (SELECT column_name FROM all_tab_cols 
WHERE OWNER='HR' AND table_name='EMPLOYEES' AND ROWNUM=level)     
SELECT X.column_name,count(X.column_name)
FROM EMPLOYEES,X
CONNECT BY LEVEL <= (SELECT count(1) FROM all_tab_cols 
WHERE OWNER='HR' AND table_name='EMPLOYEES')
group by X.column_name;

Error at Command Line:14 Column:5 Error report: SQL Error: ORA-01788: CONNECT BY clause required in this query block 01788. 00000 - "CONNECT BY clause required in this query block"

Cud u correct the query for achieving the same?

score 0 · Answer 1 · answered Mar 03 '16 at 10:50

Your query has some issues:

subquery in with clause does not see level from other subquery,
you cannot use where rownum = 2, you have to make alias for this column and select it from inner query, but this part is not needed in your case,
hierarchical suqbquery is not needed at all if you want to check all columns.

Corrected query would be:

with x as (
  select column_name from all_tab_cols 
    where owner='HR' and table_name='EMPLOYEES')
select x.column_name, count(x.column_name) as cnt
  from x, employees group by x.column_name;

... but it always shows number of ALL rows in employees for each column. So if you want to find columns where not all data is filled, you need dynamic solution. You can add something like ...where ||'r.column_name' is null in variable v_sql:

declare
  v_sql varchar2(1000);
  v_num number;
begin
  for r in (select column_name from all_tab_cols 
            where owner='HR' and table_name='EMPLOYEES'
            order by column_id)
  loop
    v_sql := 'select count('||r.column_name||') from employees';
    execute immediate v_sql into v_num;
    dbms_output.put_line(rpad(r.column_name,30)||' '||v_num);
  end loop;
end;

Example data and output:

create table employees (id number(3), name varchar2(10), hire_date date);
insert into employees values (1, 'John', trunc(sysdate));
insert into employees values (2, 'Paul', trunc(sysdate));
insert into employees values (3, 'Mark', trunc(sysdate)-1);
insert into employees values (4, 'Anna', null);

ID                             4
NAME                           4
HIRE_DATE                      3

Query to find the Columns without data from a table Dynamically

1 Answers1