Archives of the TeradataForum

Message Posted: Fri, 30 Jan 2015 @ 14:34:39 GMT

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

Hi all,

Depends of your TD version, maybe this can Help you.

     select
     databasename
     ,Tablename
     ,ColumnName
     ,CASE
          WHEN orden = 1 and nextTable = Tablename
             THEN 'COLLECT STATS COLUMN ( ' || trim(columnname) || ') '
          WHEN orden = 1 and nextTable <> Tablename
             THEN 'COLLECT STATS COLUMN ( ' || trim(columnname) || ') ON ' || trim(databasename) || '.' || trim(TableName) || ';'
          WHEN nextTable <> Tablename
             THEN ',COLUMN( ' || trim(ColumnName) || ' ) ON ' || trim(databasename) || '.' || trim(TableName) || ';'
             ELSE ',COLUMN( ' ||  trim(ColumnName) || ') '
     END as StatsCMD
     from
     (
     select
     databasename
     ,Tablename
     ,ColumnName
     ,ROW_NUMBER() over(partition by  databasename ,Tablename order by Databasename,
     Tablename, Columnname)  as orden
     ,MAX(Tablename) over(partition by  databasename order by Databasename, Tablename,
     Columnname rows between 1 following and 1 following)  as nextTable from dbc.statsv where
     databasename in ('XXXXXX') and ColumnName is not null
     ) tmp
     order by 1,2,3

Take Care..

Antonio Pirri
Professional Services Consultant
Teradata Argentina

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