ILLEGAL USE OF KEYWORD EXCEPT

Rishi Khare · New User Joined: 13 Aug 2007 Posts: 16 Location: Mumbai

i need to compare two tables

SELECT COUNT(*) FROM (
(SELECT * FROM t1 AS A
EXCEPT(SELECT * FROM t2) AS B
UNION ALL
(SELECT * FROM t2) AS C
EXCEPT(SELECT * FROM t1) AS D
)

DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD EXCEPT. TOKEN FULL
LEFT INNER RIGHT WAS EXPECTED

Robert Sample · Posted: Tue Oct 14, 2008 5:51 pm

Even for a non-DB2 person like me, the message is quite clear -- it wants FULL, LEFT, INNER, or RIGHT and you gave it EXCEPT. Fix the problem and proceed.

expat · Posted: Tue Oct 14, 2008 5:54 pm

And for another non DB2 person like me - it is pretty clear that this should have been perhaps posted in the DB2 forum !!!

Suresh Ponnusamy · Posted: Tue Oct 14, 2008 10:35 pm

Hi Rishi

Your synax is wrong. Probably try the below one.
SELECT COUNT(*)
FROM
(
(
(SELECT * FROM t1 A)
EXCEPT
(SELECT * FROM t2 B)
) E
UNION ALL
(
(SELECT * FROM t2 C)
EXCEPT
(SELECT * FROM t1 D)
) F
) G;;

Please note that I have not tested the above query since I am not having DB2 9 in my system. EXCEPT is DB2 9 function.

Suggestions are welcome.