Archives of the TeradataForum

Message Posted: Wed, 22 Aug 2007 @ 11:10:28 GMT

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

Ooops, my previous post omitted the bteq example, sorry 'bout that. Here is the corrected one.

I'm not sure exactly what your environment is. When you say an ODBC application do you mean SQL Assistant? If so, SQL Assistant doesn't really work like that. You can however, run your initial SQL copy and paste the results into the query window and then run that.

Is that what you mean?

You can achieve a similar result in bteq. For example using this sequence:

     .set titledashes off
     .export report file=show.sql
     select 'show table ' || trim (both from tablename) || ';' (title '')
     from dbc.tables
     where databasename = 'some_db' and
             tablename like 'a%';

     .export reset
     .run file=show.sql

The above produces a bunch of show table queries and then runs them.

If you are writing your own ODBC application then simply accumulate the rows one by one (e.g. in an array) then submit them via the connection you used to get the initial result set. The complexity of your initial query is largely irrelevant as your process is:

1) submit initial query (whether it is 3 lines or 10,000 lines of SQL) to get list of queries to run

2) Run each of the returned queries and capture the individual result sets

3) Display each result set to user.

You also mention doing this in a macro or a procedure. I don't think this will work because:

1) Macros can not run dynamic SQL (i.e. the queries returned from the first query and

2) Procedures can not (currently) return result sets to you (but can run dynamic SQL). A future version of TD will allow procedures to return a result set (I don't know if that will be plural i.e. >1 result set).

BTW. I know you say your initial query has more rules than you present here, but are you sure you really want to show the entire contents of every table in a database to a user? I suspect you might be accused of information overload - even if you just sample a subset of data from each table.

If none of these are appropriate, perhaps you could provide a little bit more detail about your goals and environment over and above "... an ODBC application ...".

All the best

Glenn Mc

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