By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

Using a column value as a table name in a query

P: 1
Greetings,

I've seen this question asked and answered here, just not completely yet.

I'm wondering how to use a column value as a table name in another query. So far it's looking like you must use dynamic SQL, external to DB2.

An example: TableX contains column TabName, which is populated by the names of tables in the database. Each of these named tables contains a field called "VID". I wish to match some value of VID in each of the tables named in the TabName column of TableX.

The closest to a working example I've seen, posted here by jefftyzzer, is this:

CREATE PROCEDURE JTYZZER.TTEST()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC TTEST
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE V_SQL VARCHAR(64);--
DECLARE V_TAB VARCHAR(128);--
DECLARE C_SQL CURSOR WITH RETURN FOR S_SQL;--
SET V_TAB = 'CODE';--
SET V_SQL = 'SELECT COUNT(*) FROM '||V_TAB||' FOR READ ONLY';--
PREPARE S_SQL FROM V_SQL;--
OPEN C_SQL;--
END;

CALL JTYZZER.TTEST();


This is close, but does not do what was asked. That being to read a column value from one table, and use that as the table name in another query.

Any help would be really appreciated.
Mar 3 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.