Search records and null

Find in a table, for any field of the table, how many records are NULL, and of these NULL how many separate by values of another field of the same table.
Example result:
TAB_NAME                      COL_NAME                      NUM_ROWS                   NUM_NULLS_TOT       NUM_NULLS_X_COLVAL               COLVAL_LE_ID
————————————————————————————-
S_REPORT                        S_TAX_BRACKET_CD                        7111600   7111599   2470            631
S_REPORT                        S_TAX_BRACKET_CD                        7111600   7111599   57859         FC222200
S_REPORT                        S_TAX_BRACKET_CD                        7111600   7111599   3038882   Z2030
S_REPORT                        S_TAX_BRACKET_CD                        7111600   7111599   4012388   C8101
 
The S_REPORT table has 7111600 rows and has a column name S_TAX_BRACKET_CD that has 7111599 NULL rows. The S_REPORT table also has a column name LE_ID which its value are 631, FC222200, Z2030 e C8101. The NULL rows are separated for column name LE_ID values in:
LE_ID=631 has 2470 NULL rows
LE_ID=FC222200 has 57859 NULL rows
LE_ID=Z2030 has 3038882 NULL rows
LE_ID=C8101 has 4012388 NULL rows
Below the SQL script code that create two tables (T_NUM_NULLS and T_NUM_NULLS_X_COLVAL), and one view (V_NUM_NULLS_X_COLVAL) where find the result after script run. Important: before the script run in your DB, modify the “nametab” and “namecol” default variable value in the script according to your research.
 
/* 
/* Procedure that find, for every column name in the tables specified in nametab variable, how many records are NULL,                  */
/* grouped by column value specified in namecol variable.                                                                              */
/* The result of the procedure are in: V_NUM_NULLS_X_COLVAL view, T_NUM_NULLS and T_NUM_NULLS_X_COLVAL tables                          */
/* Modify how would you prefer the default nametab and namecol values                                                                  */
/*                                                                                                                                     */
/* NOTICES:                                                                                                                            */
/* 1) The column name contents in namecol variable must be present in all tables where you execute the research of NULL values.        */
/* 2) The procedure has locked the data type dimension in the EXECUTE IMMEDIATE strings – this could be cause run errors.              */
/* 3) Please rispect the standard naming rules of Oracle database objects for the content of the nametab and namecol variables,        */
/*    otherwise the procedure will return an error.                                                                                    */
DECLARE
cnt INTEGER;— Variabile 0 o 1 numero record trovati – 0 significa che la tabella T_NUM_NULLS non esiste
nametab VARCHAR2(30)default’S_REPORT%’;— Name/s table/s string variable
namecol VARCHAR2(30)default’LE_ID’;— Column name string variable that contains the categories to distinguish NULLS
numtabtot INTEGER;
numtabok INTEGER;
BEGIN
 
— Variables values check
nametab :=REPLACE(nametab,’ ‘,”);
namecol :=REPLACE(namecol,’ ‘,”);
namecol :=REPLACE(namecol,’%’,”);
 
— Check if exists the T_NUM_NULLS table. If exists, drop and create it, otherwise create it.
SELECT count(*)INTO cnt FROM USER_TABLES WHERE table_name =’T_NUM_NULLS’;
 
IF cnt =0
THEN
EXECUTEIMMEDIATE’create table T_NUM_NULLS (TAB_NAME VARCHAR2(30), COL_NAME VARCHAR2(30), NUM_ROWS NUMBER, NUM_NULLS NUMBER)’;
ELSE
EXECUTEIMMEDIATE’drop table T_NUM_NULLS’;
EXECUTEIMMEDIATE’create table T_NUM_NULLS (TAB_NAME VARCHAR2(30), COL_NAME VARCHAR2(30), NUM_ROWS NUMBER, NUM_NULLS NUMBER)’;
ENDIF;
— Check if exists the T_NUM_NULLS_X_COLVAL table. If exists, drop and create it, otherwise create it.
SELECT count(*)INTO cnt FROM USER_TABLES WHERE table_name =’T_NUM_NULLS_X_COLVAL’;
 
IF cnt =0
THEN
EXECUTEIMMEDIATE’create table T_NUM_NULLS_X_COLVAL (TAB_NAME VARCHAR2(30), COL_NAME VARCHAR2(30), NUM_NULLS NUMBER, COLVAL_’ || namecol || ‘ VARCHAR2(8))’;
ELSE
EXECUTEIMMEDIATE’drop table T_NUM_NULLS_X_COLVAL’;
EXECUTEIMMEDIATE’create table T_NUM_NULLS_X_COLVAL (TAB_NAME VARCHAR2(30), COL_NAME VARCHAR2(30), NUM_NULLS NUMBER, COLVAL_’ || namecol || ‘ VARCHAR2(8))’;
ENDIF;
— Fills numtabok and numtabtot variables to check that the column name content in namecol variable, exists in all tables where we doing the research of NULLS, in order to avoid below query errors.
SELECT COUNT(*)INTO numtabok
FROM
(
SELECTDISTINCT(table_name)
FROM ALL_TAB_COLUMNS
WHERE1=1
AND TABLE_NAME LIKE” || nametab || ”
)
;
SELECT COUNT(*)INTO numtabtot
FROM
(
SELECTDISTINCT(t2.table_name), t2.COLUMN_NAME
FROM
(
SELECTDISTINCT(table_name), COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE1=1
AND table_name LIKE” || nametab || ”
) t1
INNERJOIN
(
SELECTDISTINCT(table_name),COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE1=1
AND table_name LIKE” || nametab || ”
AND COLUMN_NAME =” || namecol || ”
) t2
ON(t1.table_name = t2.table_name)
)
;
 
— Creating view that contains the procedure result.
EXECUTEIMMEDIATE’CREATE OR REPLACE VIEW V_NUM_NULLS_X_COLVAL AS SELECT t1.TAB_NAME, t1.COL_NAME, t1.NUM_ROWS, t1.NUM_NULLS NUM_NULLS_TOT, t2.NUM_NULLS NUM_NULLS_X_COLVAL, t2.COLVAL_’ || namecol || ‘ FROM T_NUM_NULLS t1 LEFT OUTER JOIN T_NUM_NULLS_X_COLVAL t2 ON t1.TAB_NAME = t2.TAB_NAME AND t1.COL_NAME = t2.COL_NAME ‘;
IF numtabtot = numtabok
THEN
FOR someone IN
(
select t1.TABLE_NAME, t2.COLUMN_NAME, t1.NUM_ROWS, t2.NUM_NULLS
from
(
select TABLE_NAME, NUM_ROWS
from USER_TABLES
where1=1
AND table_name IN
(
SELECTDISTINCT(table_name)
FROM ALL_TAB_COLUMNS
WHERE1=1
AND table_name LIKE” || nametab || ”
)
) t1
INNERJOIN
(
select TABLE_NAME, COLUMN_NAME, NUM_NULLS
from ALL_TAB_COL_STATISTICS
where1=1
AND table_name IN
(
SELECTDISTINCT(table_name)
FROM ALL_TAB_COLUMNS
WHERE1=1
AND table_name LIKE” || nametab || ”
)
) t2
on t1.TABLE_NAME = t2.TABLE_NAME
)
LOOP
EXECUTEIMMEDIATE’insert into T_NUM_NULLS (TAB_NAME, COL_NAME, NUM_ROWS, NUM_NULLS) values (”’ || someone.TABLE_NAME || ”’, ”’ || someone.COLUMN_NAME || ”’, ”’ || someone.NUM_ROWS || ”’, ”’ || someone.NUM_NULLS || ”’)’;
EXECUTEIMMEDIATE’insert into T_NUM_NULLS_X_COLVAL (TAB_NAME, COL_NAME, NUM_NULLS, COLVAL_’ || namecol || ‘) ‘ || ‘select ”’ || someone.TABLE_NAME || ”’ TABLE_NAME, ”’ || someone.COLUMN_NAME || ”’ COLUMN_NAME, count(*) NUM_NULLS, ‘ || namecol || ‘ from ‘ || someone.TABLE_NAME || ‘ where ‘ || someone.COLUMN_NAME || ‘ is null group by ‘ || namecol || ”;
ENDLOOP;
ELSE
FOR someone IN
(
select t1.TABLE_NAME, t2.COLUMN_NAME, t1.NUM_ROWS, t2.NUM_NULLS
from
(
select TABLE_NAME, NUM_ROWS
from USER_TABLES
where1=1
AND table_name IN
(
SELECTDISTINCT(table_name)
FROM ALL_TAB_COLUMNS
WHERE1=1
AND table_name LIKE” || nametab || ”
)
) t1
INNERJOIN
(
select TABLE_NAME, COLUMN_NAME, NUM_NULLS
from ALL_TAB_COL_STATISTICS
where1=1
AND table_name IN
(
SELECTDISTINCT(table_name)
FROM ALL_TAB_COLUMNS
WHERE1=1
AND table_name LIKE” || nametab || ”
)
) t2
on t1.TABLE_NAME = t2.TABLE_NAME
)
LOOP
EXECUTEIMMEDIATE’insert into T_NUM_NULLS (TAB_NAME, COL_NAME, NUM_ROWS, NUM_NULLS) values (”’ || someone.TABLE_NAME || ”’, ”’ || someone.COLUMN_NAME || ”’, ”’ || someone.NUM_ROWS || ”’, ”’ || someone.NUM_NULLS || ”’)’;
/* Compared to the previous FOR LOOP, isn’t executed the “insert into T_NUM_NULLS_X_COLVAL” because at least one of tables of research doesn’t have the search topic column name. */
ENDLOOP;
ENDIF;
END;
/