ORDER BY column TIMEC or expression not in result table?

0
votes

So I've got 2 queries:

Query 1:

SELECT        
a.memno, 
a.name, 
a.addr1, 
a.addr2, 
a.city, 
a.state, 
a.zip, 
a.sex, 
a.lname, 
a.ssan, 
b.addr1 AS old_addr1, 
b.addr2 AS old_addr2, 
b.city AS old_city, 
b.state AS old_state, 
b.zip AS old_zip

FROM            LIB1.TABLE1 a, LIB2.TABLE2 b

WHERE        (a.memno = b.memno) AND 

(b.groupid = 'P2') AND 
(b.type = 'B') AND 
(b.datec = 20131203) AND 
(a.addr1 <> b.addr1) AND 
(a.addr2 <> b.addr2) AND 
(a.city <> b.city) AND 
(a.state <> b.state) AND 
(a.zip <> b.zip)

ORDER BY b.timec DESC

Returns 1 record.

Query 2:

SELECT        
a.memno, 
a.name, 
a.addr1, 
a.addr2, 
a.city, 
a.state, 
a.zip, 
a.sex, 
a.lname, 
a.ssan, 
b.addr1 AS old_addr1, 
b.addr2 AS old_addr2, 
b.city AS old_city, 
b.state AS old_state, 
b.zip AS old_zip

FROM            LIB2.TABLE1 a, LIB2.TABLE2 b

WHERE        (a.memno = b.memno) AND 
(b.groupid = 'N2') AND 
(b.type = 'B') AND 
(b.datec = 20131203) AND 
(a.addr1 <> b.addr1) AND 
(a.addr2 <> b.addr2) AND 
(a.city <> b.city) AND 
(a.state <> b.state) AND 
(a.zip <> b.zip)

ORDER BY b.timec DESC

Returns 2 Records.

I'm trying to do a UNION to have these 2 queries return 1 result set.

Attempted UNION:

SELECT        
a.memno, 
a.name, 
a.addr1, 
a.addr2, 
a.city, 
a.state, 
a.zip, 
a.sex, 
a.lname, 
a.ssan, 
b.addr1 AS old_addr1, 
b.addr2 AS old_addr2, 
b.city AS old_city, 
b.state AS old_state, 
b.zip AS old_zip

FROM            LIB1.TABLE1 a, LIB2.TABLE2 b

WHERE        (a.memno = b.memno) AND 

(b.groupid = 'P2') AND 
(b.type = 'B') AND 
(b.datec = 20131203) AND 
(a.addr1 <> b.addr1) AND 
(a.addr2 <> b.addr2) AND 
(a.city <> b.city) AND 
(a.state <> b.state) AND 
(a.zip <> b.zip)

UNION

SELECT        
a.memno, 
a.name, 
a.addr1, 
a.addr2, 
a.city, 
a.state, 
a.zip, 
a.sex, 
a.lname, 
a.ssan, 
b.addr1 AS old_addr1, 
b.addr2 AS old_addr2, 
b.city AS old_city, 
b.state AS old_state, 
b.zip AS old_zip

FROM            LIB2.TABLE1 a, LIB2.TABLE2 b

WHERE        (a.memno = b.memno) AND 
(b.groupid = 'N2') AND 
(b.type = 'B') AND 
(b.datec = 20131203) AND 
(a.addr1 <> b.addr1) AND 
(a.addr2 <> b.addr2) AND 
(a.city <> b.city) AND 
(a.state <> b.state) AND 
(a.zip <> b.zip)

ORDER BY timec DESC

This gives me:

Error in WHERE clause near 'DESC'. Unable to parse query text.

Followed by:

SQL Execution Error.

Executed SQL Statement: SELECT a.memno, a.name, a.addr1, a.addr2, a.city, a.state, a.zip, a.sex, a.lname, a.ssan, b.addr1 AS old_addr1, b.addr2 AS old_addr2, b.city AS old_city, b.state AS old_state, b.zip AS old_zip FROM LIB1.TABLE1 a, LIB2.TABLE2 b WHERE (a.mem...

Error Source: CWBODB.DLL

Error Message: ERROR [42000][IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0199 - Keyword DESC not expected. Valid tokens: FOR SKIP WTIH FETCH ORDER UNION EXCEPT OPTIMIZE.

EDIT:

Bangs Head Can't believe I missed that I had 'desc' at the end of first query still. After getting rid of the 'desc' at the end of the first query, I now get the same error, but with Message: Column TIMEC cannot be qualified.

EDIT2:

Message: ORDER BY column TIMEC or expression not in result table.

2
sqlibm-midrangedb2-400
You've got desc twice in your query, once on line 3, once on line 8. Are you aware of that? The error is about the first one, not the second one. – user743382
Format your queries so they're easier to read -- ex: put each select field on it's own line -- which will help in general, and especially with UNIONS when you need to be extra careful with field numbers and field ordering. – Chains
I edited for query formatting. Better? – Analytic Lunatic

2 Answers

3
votes

First query of the union, you left desc at the end of the last line:

WHERE a.memno [...snip...] <> b.zipc desc  
                                     ^^^^

... which is exactly what the error message said. just because you can see a (valid) DESC at the end of the query, doesn't mean there can't be a desc somewhere else... This is especially true when your query is a couple miles wide.

0
votes

In adition to the rogue DESC in your first WHERE clause, ORDER BY is applied to the resulting union (which does not have a timec column), not the source datasets.

You can either union the two queries as subquery:

SELECT * FROM
    (SELECT a.memno, a.name, 
            a.addr1, a.addr2, a.city, a.state, a.zip, a.sex, a.lname, a.ssan, 
            b.addr1 as old_addr1, b.addr2 as old_addr2, b.city as old_city, b.state as old_state, b.zip as old_zip 
    FROM LIB1.TABLE1 a, LIB1.TABLE2 b 
    WHERE a.memno = b.memno 
      and b.groupid = 'P2' 
      and b.type = 'B' 
      and b.datec =  20131205 
      AND a.addr1 <> b.addr1 
      AND a.addr2 <> b.addr2 
      AND a.city <> b.city 
      AND a.state <> b.state 
      AND a.zip <> b.zipc 
    ORDER BY b.timec desc  
    ) A
UNION
SELECT * FROM    
    (
    SELECT a.memno, a.name, 
           a.addr1, a.addr2, a.city, a.state, a.zip, a.sex, a.lname, a.ssan, 
           b.addr1 as old_addr1, b.addr2 as old_addr2, b.city as old_city, b.state as old_state, b.zip as old_zip 
    FROM LIB2.TABLE1 a, LIB2.TABLE2 b 
    WHERE a.memno = b.memno 
      and b.groupid = 'N2' 
      and b.type = 'B' 
      and b.datec = 20131205 
      AND a.addr1 <> b.addr1 
      AND a.addr2 <> b.addr2 
      AND a.city <> b.city 
      AND a.state <> b.state 
      AND a.zip <> b.zip 
    ORDER BY b.timec desc
    ) B