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

SQL challenge in DB2 v8.2 environment

P: n/a
smd
Hello and thanks for taking the time to help out. I've been tasked
with finding records from one table that are not in another table, but
do exist in a third. I've written a query that identifies the records,
but when I try to add the meaningful data to the select statement, I
find that because I am using "EXCEPT" I needed to add another join (my
selection column list did not match the column list in the EXPCEPT
selection column list) and that's where my problems start! Below find
a simplified version of the SQL I am trying to execute:

SELECT DISTINCT A.XYZ, B.WUV
FROM SCHEMA1.TABLE1 A
INNER JOIN (
SELECT WUV
FROM SCHEMA2.TABLE1) B
ON A.XYZ = B.WUV
INNER JOIN (
SELECT DISTINCT C.WUV
FROM SCHEMA2.TABLE1 C
INNER JOIN SCHEMA1.TABLE1 D
ON C.WUV = D.XYZ
EXCEPT
SELECT DISTINCT E.XYZ
FROM SCHEMA2.TABLE3 E
WHERE E.DATE = (
SELECT F.DATE
FROM SCHEMA2.TABLE1 F
)
AND E.ID = 654321
) XX
ON A.WUV = XX.WUV
WHERE A.ID = 123456
AND A.FLAG = 'Y'
AND A.CODE = 'LMNOP'
When executing this statement the following error is returned:

[[DB2/AIX] SQL0206N "XX.WUV" is not valid in the context where it is
used. SQLSTATE=42703

The problem seems to be with the second INNER JOIN:

....
INNER JOIN (
SELECT DISTINCT C.WUV
FROM SCHEMA2.TABLE1 C
INNER JOIN SCHEMA1.TABLE1 D
ON C.WUV = D.XYZ
EXCEPT
SELECT DISTINCT E.XYZ
FROM SCHEMA2.TABLE3 E
WHERE E.DATE = (
SELECT F.DATE
FROM SCHEMA2.TABLE1 F
)
AND E.ID = 654321
) XX
ON A.WUV = XX.WUV
....

So I ran the sub query within the INNER JOIN:

SELECT DISTINCT C.WUV
FROM SCHEMA2.TABLE1 C
INNER JOIN SCHEMA1.TABLE1 D
ON C.WUV = D.XYZ
EXCEPT
SELECT DISTINCT E.XYZ
FROM SCHEMA2.TABLE3 E
WHERE E.DATE = (
SELECT F.DATE
FROM SCHEMA2.TABLE1 F
)
AND E.ID = 654321

This returned the records I was looking for. The interesting thing is
that the column name was "1". I tried the same sub query and changed
the first line to:

SELECT DISTINCT C.WUV AS WUV_DUM

The column name was still "1".
I believe that this is what is at the heart of my problem. In the ON
part of the JOIN, XX.WUV is not recognized because there appears to be
no XX.WUV.

I'm stumped and could use any and all help to write this correctly!

Thanks!

Jun 1 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
smd wrote:
Hello and thanks for taking the time to help out. I've been tasked
with finding records from one table that are not in another table, but
do exist in a third. I've written a query that identifies the records,
but when I try to add the meaningful data to the select statement, I
find that because I am using "EXCEPT" I needed to add another join (my
selection column list did not match the column list in the EXPCEPT
selection column list) and that's where my problems start! Below find
a simplified version of the SQL I am trying to execute:

SELECT DISTINCT A.XYZ, B.WUV
FROM SCHEMA1.TABLE1 A
INNER JOIN (
SELECT WUV
FROM SCHEMA2.TABLE1) B
ON A.XYZ = B.WUV
INNER JOIN (
SELECT DISTINCT C.WUV
FROM SCHEMA2.TABLE1 C
INNER JOIN SCHEMA1.TABLE1 D
ON C.WUV = D.XYZ
EXCEPT
SELECT DISTINCT E.XYZ
FROM SCHEMA2.TABLE3 E
WHERE E.DATE = (
SELECT F.DATE
FROM SCHEMA2.TABLE1 F
)
AND E.ID = 654321
) XX
ON A.WUV = XX.WUV
WHERE A.ID = 123456
AND A.FLAG = 'Y'
AND A.CODE = 'LMNOP'
When executing this statement the following error is returned:

[[DB2/AIX] SQL0206N "XX.WUV" is not valid in the context where it is
used. SQLSTATE=42703

The problem seems to be with the second INNER JOIN:

...
INNER JOIN (
SELECT DISTINCT C.WUV
FROM SCHEMA2.TABLE1 C
INNER JOIN SCHEMA1.TABLE1 D
ON C.WUV = D.XYZ
EXCEPT
SELECT DISTINCT E.XYZ
FROM SCHEMA2.TABLE3 E
WHERE E.DATE = (
SELECT F.DATE
FROM SCHEMA2.TABLE1 F
)
AND E.ID = 654321
) XX
ON A.WUV = XX.WUV
...

So I ran the sub query within the INNER JOIN:

SELECT DISTINCT C.WUV
FROM SCHEMA2.TABLE1 C
INNER JOIN SCHEMA1.TABLE1 D
ON C.WUV = D.XYZ
EXCEPT
SELECT DISTINCT E.XYZ
FROM SCHEMA2.TABLE3 E
WHERE E.DATE = (
SELECT F.DATE
FROM SCHEMA2.TABLE1 F
)
AND E.ID = 654321

This returned the records I was looking for. The interesting thing is
that the column name was "1". I tried the same sub query and changed
the first line to:

SELECT DISTINCT C.WUV AS WUV_DUM

The column name was still "1".
I believe that this is what is at the heart of my problem. In the ON
part of the JOIN, XX.WUV is not recognized because there appears to be
no XX.WUV.

I'm stumped and could use any and all help to write this correctly!

Thanks!

Somewhat simpler, you could depend on primary keys not being null like this:

select <stuff from table1>
from table1
left outer join table2 on <some stuff>
join table3 on <otherstuff>
where table2.primarykey is null

The left outer join will cause nulls to be returned for nonmatching rows
and the join allows only rows wit data from both tables in the answer
set, so you get rows from table1 without match in table2 but with a
match in table3.
Jun 1 '06 #2

P: n/a

smd wrote:
Hello and thanks for taking the time to help out. I've been tasked
with finding records from one table that are not in another table, but
do exist in a third. I've written a query that identifies the records,
but when I try to add the meaningful data to the select statement, I
find that because I am using "EXCEPT" I needed to add another join (my
selection column list did not match the column list in the EXPCEPT
selection column list) and that's where my problems start! Below find
a simplified version of the SQL I am trying to execute:

SELECT DISTINCT A.XYZ, B.WUV
FROM SCHEMA1.TABLE1 A
INNER JOIN (
SELECT WUV
FROM SCHEMA2.TABLE1) B
ON A.XYZ = B.WUV
INNER JOIN (
SELECT DISTINCT C.WUV
FROM SCHEMA2.TABLE1 C
INNER JOIN SCHEMA1.TABLE1 D
ON C.WUV = D.XYZ
EXCEPT
SELECT DISTINCT E.XYZ
FROM SCHEMA2.TABLE3 E
WHERE E.DATE = (
SELECT F.DATE
FROM SCHEMA2.TABLE1 F
)
AND E.ID = 654321
) XX
ON A.WUV = XX.WUV
WHERE A.ID = 123456
AND A.FLAG = 'Y'
AND A.CODE = 'LMNOP'
When executing this statement the following error is returned:

[[DB2/AIX] SQL0206N "XX.WUV" is not valid in the context where it is
used. SQLSTATE=42703

The problem seems to be with the second INNER JOIN:

...
INNER JOIN (
SELECT DISTINCT C.WUV
FROM SCHEMA2.TABLE1 C
INNER JOIN SCHEMA1.TABLE1 D
ON C.WUV = D.XYZ
EXCEPT
SELECT DISTINCT E.XYZ
FROM SCHEMA2.TABLE3 E
WHERE E.DATE = (
SELECT F.DATE
FROM SCHEMA2.TABLE1 F
)
AND E.ID = 654321
) XX
ON A.WUV = XX.WUV
...

So I ran the sub query within the INNER JOIN:

SELECT DISTINCT C.WUV
FROM SCHEMA2.TABLE1 C
INNER JOIN SCHEMA1.TABLE1 D
ON C.WUV = D.XYZ
EXCEPT
SELECT DISTINCT E.XYZ
FROM SCHEMA2.TABLE3 E
WHERE E.DATE = (
SELECT F.DATE
FROM SCHEMA2.TABLE1 F
)
AND E.ID = 654321

This returned the records I was looking for. The interesting thing is
that the column name was "1". I tried the same sub query and changed
the first line to:

SELECT DISTINCT C.WUV AS WUV_DUM

The column name was still "1".
I believe that this is what is at the heart of my problem. In the ON
part of the JOIN, XX.WUV is not recognized because there appears to be
no XX.WUV.

I'm stumped and could use any and all help to write this correctly!

Thanks!

Hello and thanks for taking the time to help out. I've been tasked
with finding records from one table that are not in another table, but
do exist in a third


What's wrong with the basic query?

SELECT * FROM TABLE1
WHERE NOT EXISTS (SELECT * FROM TABLE 2 WHERE...)
AND EXISTS (SELECT * FROM TABLE3 WHERE...)

B.

Jun 5 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.