Using a column value as a table name in a query

[Joe Meng]

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.