All Forums Database
v_gabrielli 33 posts Joined 04/10
24 Oct 2013
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
 

Vince
gkatiyar 19 posts Joined 09/13
24 Oct 2013

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.

Regards,
Gaurav Katiyar

Raja_KT 1246 posts Joined 07/09
24 Oct 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

v_gabrielli 33 posts Joined 04/10
25 Oct 2013

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

Vince

M.Saeed Khurram 544 posts Joined 09/12
25 Oct 2013

Hi Vince,
Can you please share your solution for others help :-)
Thanks,
 

Khurram

v_gabrielli 33 posts Joined 04/10
25 Oct 2013

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

Vince

Qaisar Kiani 337 posts Joined 11/05
27 Oct 2013

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

v_gabrielli 33 posts Joined 04/10
30 Oct 2013

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")

Vince

You must sign in to leave a comment.