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.

devx777@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.

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:
> devx777@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.

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:
> > devx777@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.

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.

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.