Archives of the TeradataForum

Message Posted: Wed, 06 May 2009 @ 16:58:59 GMT

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

Maybe I can help teach you to fish instead. If you don't know how to use OLAP/ordered analytic functions, learn now. They're the best thing since sliced bread. (Other list members - what do you think? Should I just write the whole thing for him?)

(I'm doing this off the top of my head, so you'll probably have to tweak it a bit.)

For the OLAP version, you need two counters in the sql:

     Row_number() over (partition by DatabaseName, TableName order by ColumnPosition) as rn,
     Count(*) over  (partition by DatabaseName, TableName) as rc

Then in the select, use case to examine them and generate the correct output

     Case when rn = 1 then 'Select from ' || trim(databaseName) || '.' ||
     trim(tableName) etc. else '' end
     ||
     Case when rn > 1 then ', ' || trim (columnName) else '' end
     || case when rn = ct the ');" else '' end
      from dbc.indices etc.

The first CASE has all the sql you want to run including the first column name. The second has just a leading comma and field name for any but the first field. The last CASE has only what you need after the last field.

If you need the list of fields more than once in the select, you have to use this approach to generate each section in separate SQLs then UNION them together (which means you'll need to leave in fields to correctly order the unioned result).

Dave

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