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

DB2 Dynamic SQL Table Name in Field Value

P: n/a
Hello,

I am trying to find some information or an example on how to build a
dynamic query in DB2 that would allow me to join a table which its name
is stored as a field value on another table.
I have done this in the past in SQL server, but DB2 is not as easy...

Anyone out there that can help me?

Your help will be much appreciated.

Thanks.

Aug 15 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a

de*****@hotmail.com wrote:
Hello,

I am trying to find some information or an example on how to build a
dynamic query in DB2 that would allow me to join a table which its name
is stored as a field value on another table.
I have done this in the past in SQL server, but DB2 is not as easy...

Anyone out there that can help me?

Your help will be much appreciated.

Thanks.
One way to do it is by DECLAREing a CURSOR but having the FOR refer to
a PREPAREd stament, gotten from a variable.

For example:

DECLARE List CURSOR WITH RETURN TO CLIENT FOR Query;
PREPARE Query FROM Text;
OPEN List;

B.

Aug 15 '06 #2

P: n/a
Just amplify what Brian has said, let's say your table name is stored
in a variable called V_TAB, you could do something like 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();
Result set 1
--------------

1
-----------
13208

1 record(s) selected.

Return Status = 0

HTH,

--Jeff

Brian Tkatch wrote:
de*****@hotmail.com wrote:
Hello,

I am trying to find some information or an example on how to build a
dynamic query in DB2 that would allow me to join a table which its name
is stored as a field value on another table.
I have done this in the past in SQL server, but DB2 is not as easy...

Anyone out there that can help me?

Your help will be much appreciated.

Thanks.

One way to do it is by DECLAREing a CURSOR but having the FOR refer to
a PREPAREd stament, gotten from a variable.

For example:

DECLARE List CURSOR WITH RETURN TO CLIENT FOR Query;
PREPARE Query FROM Text;
OPEN List;

B.
Aug 15 '06 #3

P: n/a
Oops, one more thing: you mentioned "a table which its name
is stored as a field value on another table." For that, you could
assign the V_TAB variable from a scalar fullselect, like so:

SET V_TAB = (SELECT x FROM y WHERE z);--

--Jeff

jefftyzzer wrote:
Just amplify what Brian has said, let's say your table name is stored
in a variable called V_TAB, you could do something like 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();
Result set 1
--------------

1
-----------
13208

1 record(s) selected.

Return Status = 0

HTH,

--Jeff

Brian Tkatch wrote:
de*****@hotmail.com wrote:
Hello,
>
I am trying to find some information or an example on how to build a
dynamic query in DB2 that would allow me to join a table which its name
is stored as a field value on another table.
I have done this in the past in SQL server, but DB2 is not as easy...
>
Anyone out there that can help me?
>
Your help will be much appreciated.
>
Thanks.
One way to do it is by DECLAREing a CURSOR but having the FOR refer to
a PREPAREd stament, gotten from a variable.

For example:

DECLARE List CURSOR WITH RETURN TO CLIENT FOR Query;
PREPARE Query FROM Text;
OPEN List;

B.
Aug 15 '06 #4

P: n/a

jefftyzzer wrote:
Oops, one more thing: you mentioned "a table which its name
is stored as a field value on another table." For that, you could
assign the V_TAB variable from a scalar fullselect, like so:

SET V_TAB = (SELECT x FROM y WHERE z);--
I think SELECT x INTO V_TAB FROM y WHERE Z, is the more standard way.

B.

Aug 15 '06 #5

P: n/a
I had read in the Janmohamed, et al., book that SET is more efficient,
but that may only be for multiple values. At any rate, both can be
used, and you may well be right that SELECT...INTO is more standard (I
haven't seen enough of others' SQL PL to get a feel for what's best
practice).

Regards,

--Jeff

Brian Tkatch wrote:
jefftyzzer wrote:
Oops, one more thing: you mentioned "a table which its name
is stored as a field value on another table." For that, you could
assign the V_TAB variable from a scalar fullselect, like so:

SET V_TAB = (SELECT x FROM y WHERE z);--

I think SELECT x INTO V_TAB FROM y WHERE Z, is the more standard way.

B.
Aug 16 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.