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

P2-500 of Informix Guild to SQL - Restrictions on a Combined SELECT - "In Dynamic Server, you cannot use a UNION operator inside a subquery" Art S. Kagel wrote: > 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

In that situation I prefer use temporary table, f.e.: SELECT viotid FROM sysviolations UNION SELECT diatid FROM sysviolations INTO TEMP sys_tmp; SELECT tabname FROM systables WHERE tabid NOT IN (SELECT viotid FROM sys_tmp); VYT >> I have a perfectly valid UNION: >> SELECT tabname >> FROM systables >> WHERE tabid NOT IN ( >> SELECT viotid FROM sysviolations >> UNION >> SELECT diatid FROM sysviolations);>> Any ideas? >> Art S. Kagel