SQL to pull data

Teradata Applications
Enthusiast

SQL to pull data

Hi, I have one scenario and I am looking for your help.

we have large DW with almost 10 different soruces with 400 tables. So, I want to know what are all sources are populating each table. 

in all these tables SRC_CD is Mandatory.

sel DISTINCT '?ACCT' as "TABLE_NM",

CASE WHEN SUM(CASE when SRC_CD='S1' THEN 1   ELSE 0 END) >=1 THEN 'Yes' else 'No' end AS  S1

,CASE when SUM(CASE when SRC_CD='S2' THEN 1   ELSE 0 END) >=1 THEN 'Yes' ELSE 'No' END AS S2

,CASE when SUM(CASE when SRC_CD='S3' THEN 1   ELSE 0 END) >=1  THEN 'Yes' ELSE 'No' END AS S3

,CASE when SUM(CASE when SRC_CD='S4' THEN 1   ELSE 0 END) >=1 THEN 'Yes' ELSE 'No' END AS S4

,CASE when SUM(CASE when SRC_CD NOT IN ('S1','S2','S3','S4') THEN 1   ELSE 0 END) >=1 THEN 'Yes' ELSE 'No' END AS UNK

 FROM

database1.?ACCT

1. this is simple query to test one table everytime.

2. I can do UNION  by writing same sql for 400 tables.

3. But I want to automate this prcoess. So, is there a way so that query will take input table name from below query.

sel dbc.tablename from dbc.columns where columnname like 'SRC_CD%' and databasename = database1

I would really appriciate your help!

-Kumar

Tags (1)
5 REPLIES
Enthusiast

Re: SQL to pull data

The below SQL will generate the SQL for all the tables in the specified database. You can either execute this in SQL Assistant and can execute the SQLs produced in the resultset or you can create a bteq script with the below mentioned SQL and output the result in another bteq script (which will be set of SQLs) and execute that one

(and of course you will need to add the login details in case you opt for bteqs)

SELECT 'SELECT ' || TRIM(TABLENAME) || ' AS TABLE_NM' ||
' ,CASE WHEN SUM(CASE when SRC_CD=''S1'' THEN 1 ELSE 0 END) >=1 THEN ''Yes'' else ''No'' end AS S1' ||
' ,CASE when SUM(CASE when SRC_CD=''S2'' THEN 1 ELSE 0 END) >=1 THEN ''Yes'' ELSE ''No'' END AS S2' ||
' ,CASE when SUM(CASE when SRC_CD=''S3'' THEN 1 ELSE 0 END) >=1 THEN ''Yes'' ELSE ''No'' END AS S3' ||
' ,CASE when SUM(CASE when SRC_CD=''S4'' THEN 1 ELSE 0 END) >=1 THEN ''Yes'' ELSE ''No'' END AS S4' ||
' ,CASE when SUM(CASE when SRC_CD NOT IN (''S1'',''S2'',''S3'',''S4'') THEN 1 ELSE 0 END) >=1 THEN ''Yes'' ELSE ''No'' END AS UNK;'
FROM DBC.TABLES
WHERE DATABASENAME = <<databasename>>
AND TABLEKIND = 'T'

Cheers!

Enthusiast

Re: SQL to pull data

Ops missed the from clause...

SELECT 'SELECT ' || TRIM(TABLENAME) || ' AS TABLE_NM' ||
' ,CASE WHEN SUM(CASE when SRC_CD=''S1'' THEN 1 ELSE 0 END) >=1 THEN ''Yes'' else ''No'' end AS S1' ||
' ,CASE when SUM(CASE when SRC_CD=''S2'' THEN 1 ELSE 0 END) >=1 THEN ''Yes'' ELSE ''No'' END AS S2' ||
' ,CASE when SUM(CASE when SRC_CD=''S3'' THEN 1 ELSE 0 END) >=1 THEN ''Yes'' ELSE ''No'' END AS S3' ||
' ,CASE when SUM(CASE when SRC_CD=''S4'' THEN 1 ELSE 0 END) >=1 THEN ''Yes'' ELSE ''No'' END AS S4' ||
' ,CASE when SUM(CASE when SRC_CD NOT IN (''S1'',''S2'',''S3'',''S4'') THEN 1 ELSE 0 END) >=1 THEN ''Yes'' ELSE ''No'' END AS UNK' ||
' FROM ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';'
FROM DBC.TABLES
WHERE DATABASENAME = <<databasename>>
AND TABLEKIND = 'T'

Enthusiast

Re: SQL to pull data

Thank you for your response, Qaisar. 

Honestly, I didn't understand the solution you provided. if you could further explain, i would really appriciate it.

Thanks much,

Kumar

Enthusiast

Re: SQL to pull data

Kumar,

Qaisar's query has hard-coded part that does not change across differnet tables. It generates one sql for each table in DBC.Tables (instead of creating the Sqls manually). You can then run the Sql generated in result to get the final output you want.

I have modified the sql to better suit your need, since you wanted all tables which have column nmaed SRC_CD

SELECT 'SELECT ' || TRIM(TABLENAME) || ' AS TABLE_NM' || ' 
,CASE WHEN SUM(CASE when SRC_CD=''S1'' THEN 1   ELSE 0 END) >=1 THEN ''Yes'' else ''No'' end AS  S1' || '
,CASE when SUM(CASE when SRC_CD=''S2'' THEN 1   ELSE 0 END) >=1 THEN ''Yes'' ELSE ''No'' END AS S2' || '
,CASE when SUM(CASE when SRC_CD=''S3'' THEN 1   ELSE 0 END) >=1  THEN ''Yes'' ELSE ''No'' END AS S3' || '
,CASE when SUM(CASE when SRC_CD=''S4'' THEN 1   ELSE 0 END) >=1 THEN ''Yes'' ELSE ''No'' END AS S4' || '
,CASE when SUM(CASE when SRC_CD NOT IN (''S1'',''S2'',''S3'',''S4'') THEN 1   ELSE 0 END) >=1 THEN ''Yes'' ELSE ''No'' END AS UNK' || '
FROM ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';'
FROM DBC.columns WHERE columnname like 'SRC_CD%' and  DATABASENAME = <<databasename>>
Enthusiast

Re: SQL to pull data

Kumar,

You can get the records from dbc.columns but you will have to join the table dbc.table to filter out the views and tables by putting the condition dbc.tables.tablekind = 'T' otherwise the queries will contain the views as well.

The query I provided will dynamically generate the SQL for your requirements relying on the information available in dbc.taables table. After executing the query, the answerset will contain the set of SQLs UNIONED together and you can execute those queries to get your final result.