DB2 SELECT EXCEPT with WHERE clause

Question

I'm trying to compare two tables in a DB2 database in z/OS using SPUFI to submit SQL queries.

I'm doing this by using EXCEPT to see the difference between two SELECT queries.

I need to filter the SELECT statement from the first query with a WHERE clause.

SELECT KEY_FIELD_1,LOOKUP_FIELD_1  
FROM TABLE_1  
WHERE FILTER_FIELD = '1'  
EXCEPT  
SELECT KEY FIELD_2,LOOKUP_FIELD_2  
FROM TABLE_2

I got results back, but it also returned an error -199 Is this because the WHERE clause is not present in the second SELECT statement?

ERROR: ILLEGAL USE OF KEYWORD EXCEPT.  
TOKEN <ERR_STMT> <WNG_STMT> GET SQL  
SAVEPOINT HOLD FREE ASSOCIATE WAS EXPECTED

no..it isn't because of the `where` missing from the second query. it would help if you show the `where` condition. — Vamsi Prabhala, Nov 18 '16 at 15:28
apologies, that wasn't very clear - have provided the exact `WHERE` statement and also added the error message — joshi123, Nov 18 '16 at 15:42

score 4 · Accepted Answer · answered Nov 18 '16 at 16:32

4

Try introducing parentheses e.g.

( SELECT KEY_FIELD_1,LOOKUP_FIELD_1  
FROM TABLE_1  
WHERE FILTER_FIELD = '1' )  
EXCEPT  
( SELECT KEY FIELD_2,LOOKUP_FIELD_2  
FROM TABLE_2 )

answered Nov 18 '16 at 16:32

onedaywhen

1 Answers1