I need to write a script to count the not null rows in each field in the each of data mart tables.
it should be possible to generate this script from the Oracle dictionary I believe,
statement can be something like this :
select "select count (" || field_name || ") from " || table_name || " where "|| field_name || " is not null ; " from [Oracle dictionary table ] where table_name like ''XXX%" ;
mabye add table_name and field_name to the result set as well, so it is easier to see where the data is missing.
That means, if we have two DM tables,
TABLE A (COL A1, COL A2, COL A3)
TABLE B (COL B1, COL B2, COL B3)
then the output of the script will be,
|select count(col A1) from Table A where colA1 is not null;|
|select count(col A2) from Table A where colA2 is not null;|
|select count(col A3) from Table A where colA3 is not null;|
|select count(col B1) from Table B where colb1 is not null;|
|select count(col B2) from Table B where colb2 is not null;|
|select count(col B3) from Table B where colb3 is not null;|
Need your kind help guys.
Thanks and Regards,
We typically don't answer Oracle specific requests in the Teradata forum.
In Teradata, however, there are multiple ways to do this via some dynamic sql and the dbc tables.
select 'select count('||trim(columnname)||') from '||trim(tablename)||' where '||trim(columnname)|| ' is not null;"
from dbc.columns where trim(databasename) = 'yourdb';
the above code (or something very close to it) should generate the queries you seek.
when you work with Oracle you need to have some knowledge of the basic metadata views :-)
ALL_TAB_COLUMNS is similar to dbc.ColumnsV
I know that :-)
I didn't use the name as its a Oracle dictionary table which may be few of TD developera are not familiar. Finally the query is like
select 'SELECT COUNT(1) FROM ' || owner ||'.'|| table_name || ' WHERE ' ||column_name|| ' IS NOT NULL;'
from all_tab_columns where owner='JEDI_DM_RDQC' and table_name like 'D_AN%'
Thanks for your response.