Please Help: How would you perform loops in Teradata

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Highlighted
Enthusiast

Please Help: How would you perform loops in Teradata

hoping someone has done this before..  otherwise I need to use SAS

 

SELECT 'show view ' || trim(databasename) || '.' || TABLENAME || ';'

        FROM   dbc.tablesv

        WHERE databasename in ('GRP_CDM_ANALYTIC_ETL',         

                 'GRP_CDM_ANALYTIC_TABLE', 

                 'GRP_CDM_ANALYTIC_DOWNSTREAM',      

                 'GRP_CDM_CAMPAIGN_DOWNSTREAM', 

                 'GRP_CDM_CAMPAIGN_ETL',         

                 'GRP_CDM_CAMPAIGN_TABLE',       

                 'GRP_CDM_PROD_DOWNSTREAM',     

               'GRP_CDM_PROD_ETL',             

                 'GRP_CDM_PROD_TABLE',           

                 'GRP_CDM_REPORT_ETL',           

                 'GRP_CDM_REPORT_TABLE', 

                 'GRP_CDM_RECAST_ETL',           

                 'GRP_CDM_RECAST_TABLE'

                  ) AND tablekind = 'V'

        ORDER BY TABLENAME;

 

Results:

1              show view GRP_CDM_PROD_DOWNSTREAM.CN_ORT_ADDS;

2              show view GRP_CDM_PROD_DOWNSTREAM.CN_ORT_MDMS;

3              show view GRP_CDM_PROD_DOWNSTREAM.CN_ORT_MIGR;

4              show view GRP_CDM_REPORT_ETL.DLR_RC_EAST;

5              show view GRP_CDM_REPORT_ETL.DLR_RC_NATIONAL;

 

I now need to look in every show view and search for a specific database such as GRP_CDM_REPORT

Does anyone know how I can?

Execute the show view and then loop through the view code to find a string. When found list which View it was found


Accepted Solutions
Teradata Employee

Re: Please Help: How would you perform loops in Teradata

Simple join dbc.tablesv to dbc.columnsV and look for the database namein the metadata

 

dave

1 ACCEPTED SOLUTION
1 REPLY
Teradata Employee

Re: Please Help: How would you perform loops in Teradata

Simple join dbc.tablesv to dbc.columnsV and look for the database namein the metadata

 

dave