Friends:

I have a query similar in structure to the following:

SELECT
T1.IBMREQD
FROM
SYSIBM.SYSDUMMY1 T1
LEFT JOIN
TABLE
(
SELECT
IBMREQD
FROM
TABLE
(
SELECT
IBMREQD
FROM
SYSIBM.SYSDUMMY1 T2
WHERE
T2.IBMREQD = T1.IBMREQD
) AS T3
)AS T4
ON
T4.IBMREQD = T1.IBMREQD

The LOJ query works fine, but if I change it to FULL OUTER JOIN (or
RIGHT JOIN), I get SQL0206N "T1.IBMREQD" is not valid in the context
where it is used. It appears that T1 is no longer visible to the T3
query.

How can I get it to be?

(DB2 UDB LUW 8.2.3)

Thanks all,

--Jeff

jefftyzzer wrote:
> Friends:
>
> I have a query similar in structure to the following:
>
> SELECT
> T1.IBMREQD
> FROM
> SYSIBM.SYSDUMMY1 T1
> LEFT JOIN
> TABLE
> (
> SELECT
> IBMREQD
> FROM
> TABLE
> (
> SELECT
> IBMREQD
> FROM
> SYSIBM.SYSDUMMY1 T2
> WHERE
> T2.IBMREQD = T1.IBMREQD
> ) AS T3
> )AS T4
> ON
> T4.IBMREQD = T1.IBMREQD
>
> The LOJ query works fine, but if I change it to FULL OUTER JOIN (or
> RIGHT JOIN), I get SQL0206N "T1.IBMREQD" is not valid in the context
> where it is used. It appears that T1 is no longer visible to the T3
> query.
>
> How can I get it to be?
What would you want it to do?
If you do a RIGHT OUTER join then the RIGHT has to be evaluated before
the left, but it can't be evaluated because the right depends on the left.
Correlation works only across LEFT and INNER joins (left because SQL
parses left to right...).

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

jefftyzzer wrote:
> Friends:
>
> I have a query similar in structure to the following:
>
> SELECT
> T1.IBMREQD
> FROM
> SYSIBM.SYSDUMMY1 T1
> LEFT JOIN
> TABLE
> (
> SELECT
> IBMREQD
> FROM
> TABLE
> (
> SELECT
> IBMREQD
> FROM
> SYSIBM.SYSDUMMY1 T2
> WHERE
> T2.IBMREQD = T1.IBMREQD
> ) AS T3
> )AS T4
> ON
> T4.IBMREQD = T1.IBMREQD
<SNIP>

Serge answered the original question, but I have to comment. Is it me or is
there an unnatural and often unneccessary dependence on the JOIN TABLE(
<some query> ) construct on this list (and other SQL related forums for that
matter)? Isn't Jeff's query above better, more simply and understandably,
and probably more efficiently implemented by a simple LEFT OUTER JOIN? Towit:

SELECT
T1.IBMREQD
FROM
SYSIBM.SYSDUMMY1 AS T1
LEFT JOIN SYSIBM.SYSDUMMY1 AS T2
ON T2.IBMREQD = T1.IBMREQD;

Art S. Kagel

Art S. Kagel wrote:
> jefftyzzer wrote:
>> Friends:
>>
>> I have a query similar in structure to the following:
>>
>> SELECT
>> T1.IBMREQD
>> FROM
>> SYSIBM.SYSDUMMY1 T1
>> LEFT JOIN
>> TABLE
>> (
>> SELECT
>> IBMREQD
>> FROM
>> TABLE
>> (
>> SELECT
>> IBMREQD
>> FROM
>> SYSIBM.SYSDUMMY1 T2
>> WHERE
>> T2.IBMREQD = T1.IBMREQD
>> ) AS T3
>> )AS T4
>> ON
>> T4.IBMREQD = T1.IBMREQD
> <SNIP>
>
> Serge answered the original question, but I have to comment. Is it me
> or is there an unnatural and often unneccessary dependence on the JOIN
> TABLE( <some query> ) construct on this list (and other SQL related
> forums for that matter)? Isn't Jeff's query above better, more simply
> and understandably, and probably more efficiently implemented by a
> simple LEFT OUTER JOIN? Towit:
>
> SELECT
> T1.IBMREQD
> FROM
> SYSIBM.SYSDUMMY1 AS T1
> LEFT JOIN SYSIBM.SYSDUMMY1 AS T2
> ON T2.IBMREQD = T1.IBMREQD;
>
> Art S. Kagel
Sure,
I think we all assume that the query was stripped to make the point.
Queries cannot always be flattened like that.
One example may be a nested ORDER BY/FETCH FIRST/DISTINCT, ... .

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

In my effort to create something that anyone can run, I ended up with
something that invites the question "what the...?" :-)

In my real query, T1 and T2 are different tables. There are several
predicates applied to T1, and I want to filter my T2 rows to just those
that match T1's.

Thanks again,

--Jeff

Serge Rielau wrote:
> jefftyzzer wrote:
> > Friends:
> >
> > I have a query similar in structure to the following:
> >
> > SELECT
> > T1.IBMREQD
> > FROM
> > SYSIBM.SYSDUMMY1 T1
> > LEFT JOIN
> > TABLE
> > (
> > SELECT
> > IBMREQD
> > FROM
> > TABLE
> > (
> > SELECT
> > IBMREQD
> > FROM
> > SYSIBM.SYSDUMMY1 T2
> > WHERE
> > T2.IBMREQD = T1.IBMREQD
> > ) AS T3
> > )AS T4
> > ON
> > T4.IBMREQD = T1.IBMREQD
> >
> > The LOJ query works fine, but if I change it to FULL OUTER JOIN (or
> > RIGHT JOIN), I get SQL0206N "T1.IBMREQD" is not valid in the context
> > where it is used. It appears that T1 is no longer visible to the T3
> > query.
> >
> > How can I get it to be?
> What would you want it to do?
> If you do a RIGHT OUTER join then the RIGHT has to be evaluated before
> the left, but it can't be evaluated because the right depends on the left.
> Correlation works only across LEFT and INNER joins (left because SQL
> parses left to right...).
>
> Cheers
> Serge
>
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
>
> IOD Conference
> http://www.ibm.com/software/data/ond...ness/conf2006/