Output of script will be a set of select queries

Database
Enthusiast

Output of script will be a set of select queries

Dear Experts,

 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 :

 spool count_fields.sql

  select "select count (" || field_name || ") from " || table_name || " where "|| field_name || " is not null ; " from  [Oracle dictionary table ]  where table_name like ''XXX%" ;

spool off

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,

Arindam

Tags (2)
3 REPLIES
Enthusiast

Re: Output of script will be a set of select queries

Arindam,

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.

Cheers!

Senior Apprentice

Re: Output of script will be a set of select queries

Hi Arindam,

when you work with Oracle you need to have some knowledge of the basic metadata views :-)

ALL_TAB_COLUMNS is similar to dbc.ColumnsV

Enthusiast

Re: Output of script will be a set of select queries

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.