Archives of the TeradataForum

Message Posted: Thu, 12 Oct 2006 @ 12:47:55 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi

You could try the following query - it is a bit unwieldy but it will work.

Cheers

Frank

     SELECT
     CASE
     WHEN SORTORDER IN ('030', '040', '045', '060')
     THEN '     ' || DML
     ELSE DML
     END AS DML


     FROM


     (
     /* insert into */
     SELECT
     '010' (char(3))
     , tab_name (char(35))
     , db_name (char(35))
       , 'INSERT INTO NEW_BOX_ROW_COUNT '  (CHAR(80))
     FROM table_driver

     UNION ALL
     /* select */
     SELECT
     '020'
     ,  tab_name
     , db_name
     , 'SELECT'
     FROM table_driver


     UNION ALL

     /* db name */
     SELECT
     '030'
     , tab_name
     , db_name
     ,  '''' ||TRIM( UPPER(db_name)) || '''' ||','

     FROM table_driver

     UNION ALL

     /* tablename */
     SELECT
     '040'
     ,  tab_name
     , db_name
     , '''' || TRIM(UPPER(tab_name)) || ''','
     FROM table_driver

     UNION ALL
     /* count(*) */
     SELECT
     '045'
     ,  tab_name
     , db_name
     , 'COUNT(*)'

     FROM table_driver

     UNION ALL

     /* from */
     SELECT
     '050'
     ,  tab_name
     , db_name
     , 'FROM'
     FROM table_driver

     UNION ALL

     /* db_name.tab_name */
     SELECT
     '060'
     ,  tab_name
     , db_name
     ,  UPPER(TRIM(db_name) || '.' || TRIM(tab_name))
     FROM table_driver

     UNION ALL

     /* group by */
     SELECT
     '065'
     ,  tab_name
     , db_name
     , 'GROUP BY 1,2'
     FROM table_driver




     UNION ALL
     /* semicolon */
     SELECT
     '070'
     ,  tab_name, db_name, ';'

     FROM table_driver


     --where tab_name = 'account_deal_risk'
     --and db_name = 'ddhwd02p'

     )derv (sortorder, tab_name, db_name, dml)

     ORDER BY db_name, tab_name, sortorder

	<Prev	Next>		<<First	<Prev	Next>	Last>>