Archives of the TeradataForum

Message Posted: Wed, 13 Jul 2005 @ 07:29:52 GMT

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

I've been watching this thread and sure there had to be a way. Here's a quick select from DBC.Tables that picks all of the tables from the DBC database and adds however many more are needed to get 200 rows. It relies on the V2R6 concept of TOP N, but there are probably ways to accomplish the same thing in V2R5 using RANK. Try this kind of approach:

     SELECT  TOP 200 *

     FROM (SELECT 1,
     TABLENAME
     , DATABASENAME
     FROM DBC.TABLES
     WHERE TABLEKIND = 'T'
     AND   DATABASENAME = 'DBC'

     UNION

     SELECT 2,
     TABLENAME
     , DATABASENAME
     FROM DBC.TABLES
     WHERE TABLEKIND = 'T'
     AND   (TABLENAME
     , DATABASENAME) NOT IN (SELECT TABLENAME
     , DATABASENAME
     FROM DBC.TABLES
     WHERE TABLEKIND = 'T'
     AND   DATABASENAME = 'DBC')) DT (SEQ_NUM,TABLENAME, DATABASENAME)

     ORDER BY 1;

Hope this helps,

Jonathan

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