RE: Syntax error trying to formulate a sub-query

the 7.1 syntax manual , page 1-194 says that the select cannot have an order by clause, into temp clause or UNION operator. Don't know if 7.3 is any different. Sathish Sadagopan -----Original Message----- From: Art S. Kagel [mailto:kagel@bloomberg.net] Sent: Wednesday, March 17, 1999 12:18 PM To: informix-list@iiug.org Subject: Syntax error trying to formulate a sub-query OK, it has been a while, but now I have a question for the community. I have a perfectly valid UNION: SELECT viotid FROM sysviolations UNION SELECT diatid FROM sysviolations; which works flawlessly stand alone. Now I want to use it to filter another select: SELECT tabname FROM systables WHERE tabid NOT IN ( SELECT viotid FROM sysviolations UNION SELECT diatid FROM sysviolations ); And I get a -201, syntax, error pointing to the UNION keyword. I look in the manual and as I expect there is nothing explicitely preventing a subquery from containing a UNION clause. Now I think "Let's just get sneaky here" and I try to create a view on the UNION, perfectly permissible and the SQL Syntax Guide even has an example, as follows: CREATE VIEW v(tid) AS SELECT viotid FROM sysviolations UNION SELECT diatid FROM sysviolations; With the idea of using that in the subquery instead. But that gets the same syntax error in the same location, ie the UNION keyword. What gives folk? Certainly I can go the kludge route, ie tabid NOT IN (SELECT viotid ...) AND tabid NOT IN (SELECT diatid ...) but who wants two subquery overheads when one will do? Any ideas? Versions: IDS 7.21UD3 and IDS 7.24UC7 Now on 7.30UC3 I CAN create the view and use it in the subquery but I still cannot use the UNION in the subquery itself. Very odd! Any ideas team? Art S. Kagel