which fields is populated which is empty in a table

Database
Enthusiast

which fields is populated which is empty in a table

hello everyone, this ia a weird request... appreciate your help...

I have a client which has 75 tables (Teradata v13) they want me to tell them which filed in every table has data?

table a

f1 has data

f2 empty

f3 empty


f4 has data

table b

f1 has data

f2 has data

f3 has data


f4 empty

...

...

how can this be done! I have an idea as plan B to use the case statement on every field.. but wow that will take time..

help! pls provide an example SQL
7 REPLIES
Enthusiast

Re: which fields is populated which is empty in a table

Hello Vince,

I think you can use Count(columnname) for this purpose-

create multiset volatile table emp1 (id smallint, name char(2), sal decimal(8,0)) on commit preserve rows;

insert into emp1 (id,sal) values (101, 5000);

insert into emp1 (id,sal) values (102, 5500);

insert into emp1 (id,sal) values (103, 1000);

insert into emp1 (id,sal) values (104, 3000);

insert into emp1 (id,sal) values (105, 6000);

sel * from emp1;

sel count(id),count(name),count(sal) from emp1;

So whenever a column has no data, Count(columnname) is going to show Zero.

Enthusiast

Re: which fields is populated which is empty in a table

Hi Vince,

I think your plan B won't take time , if you know how to automate the script and using excel together :) , if you are comfortable with excel script. It is just a few minute's work.

Cheers,

Raja

Enthusiast

Re: which fields is populated which is empty in a table

thank you all, I have taken your advice and worked with Excel, BTEQ I have my results.. much appreciated..

Vince

Enthusiast

Re: which fields is populated which is empty in a table

Hi Vince,

Can you please share your solution for others help :-)

Thanks,

Khurram
Enthusiast

Re: which fields is populated which is empty in a table

sure thing.. first thing Monday morning when I get to the office...

Enthusiast

Re: which fields is populated which is empty in a table

How about using recursive sql with dbc.columnd table to dynamically generate count sql, which you can execute again to get the desired results...
Enthusiast

Re: which fields is populated which is empty in a table

1 use Excel to build the SQL statements 

     table name field name               Select TACCOUNT',

     TACCOUNT ACCOUNT_ID                 ="COUNT(" & C40 & "),"

     TACCOUNT CREATION_DTTM            ="COUNT(" & C41 & "),"

     TACCOUNT LOB_CD                          ="COUNT(" & C42 & "),"

     TACCOUNT SOURCE_SYSTEM_CD     ="COUNT(" & C43 & "),"

                                                           from GRP_DBMC.TACCOUNT;

etc… 75 other tables 

2 logon to BTEQ 

execute the SQL script created in Step 1 

.logon prod/.........

.SET WIDTH 2000

.export report file=C:\Users\gabrielli.vincenzo\Documents\Grand Maison\MensaEmptyFields.out

Select 'TACCOUNT',

  COUNT(ACCOUNT_ID),

  COUNT(CREATION_DTTM),

  COUNT(LOB_CD),

  COUNT(SOURCE_SYSTEM_CD)

from GRP_DBMC.TACCOUNT;

Select 'TCUSTOMER_REL', 

    COUNT(CREATION_DTTM), 

    COUNT(CUSTOMER_RELATIONSHIP_ID), 

    COUNT(LOCATION_ID), 

    COUNT(PARTY_ID) 

from GRP_DBMC.TCUSTOMER_REL; 

etc… 75 other tables 

3 Copy results back into Excel

'TACCOUNT',Count(ACCOUNT_ID),Count(CREATION_DTTM),Count(LOB_CD),Count(SOURCE_SYSTEM_CD)

TACCOUNT,13538718,0,13538718,0

4 Use excel to parse the fields… use comma as the delimiter…

5 If the fields has zero then they are the empty ones (no data) 

6 Use the IF statement to flag the condition 

=IF(D21>0,"Yes field contains data","field is empty")