SEO

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!

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.

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.