Archives of the TeradataForum

Message Posted: Mon, 29 Jan 2007 @ 20:28:33 GMT

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

The results aren't pretty (although I'm sure they could be made so if needed), but this should give you a workable script. Not sure what you wanted to outer join to, though, so I guess I might be missing something....

     select
     case when ColumnPosition = 1 then 'collect stats on ' ||
     TRIM(DatabaseName) || '.' || TRIM(TableName) || ' index  (' || ColumnName
          else ', ' || TRIM(ColumnName) end
     ||
     case when ColumnPosition = max_col then ');'
     else '' end
     from
     (select DatabaseName, TableName, IndexNumber, ColumnPosition,
     ColumnName, max(ColumnPosition) over (partition by DatabaseName, TableName,
     IndexNumber) max_col
     from dbc.indices) c
     order by DatabaseName, TableName, IndexNumber, ColumnPosition;

Dave Curley

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