ORDER BY column TIMEC or expression not in result table?

Question

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.

Answer 1

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.

Answer 2

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