Search records and null

Search records and null
  • 24
    Set

Search records and null

twittergoogle_pluslinkedin

Trovare, per un campo all’interno di una tabella, quanti record sono NULL, e contare inoltre quanti sono i NULL raggruppati secondo il valore di un altro campo.
Risultato di esempio:
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

La tabella S_REPORT ha 7111600 righe e la sua colonna S_TAX_BRACKET_CD contiene 7111599 righe NULL. La tabella S_REPORT ha anche una colonna LE_ID i cui valori sono 631, FC222200, Z2030 e C8101. Vogliamo mostrare, per ogni valore della colonna LE_ID, la corrispondente quantità di NULL della colonna S_TAX_BRACKET_CD:
LE_ID=631 avrà 2470 righe NULL
LE_ID=FC222200 avrà 57859 righe NULL
LE_ID=Z2030 avrà 3038882 righe NULL
LE_ID=C8101 avrà 4012388 righe NULL
Qui sotto il codice SQL che crea due tabelle (T_NUM_NULLS e T_NUM_NULLS_X_COLVAL) e la vista (V_NUM_NULLS_X_COLVAL) dove troveremo i risultati dopo il lancio degli script. Importante: prima di lanciare lo script sul proprio DB; modificare il valore di default delle variabili “nametab” e “namecol” a seconda della ricerca che si desidera fare.

/*
/* 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;
/