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

TABLE function, OUTER JOINS, and SQL0206N

P: n/a
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

Aug 23 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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/
Aug 23 '06 #2

P: n/a
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
Aug 23 '06 #3

P: n/a
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/
Aug 23 '06 #4

P: n/a
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/
Aug 23 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.