Re: Syntax error trying to formulate a sub-query

It is clearly documented that 'In IDS, union operator is not allowed inside a subquery' under the section 'Restrictions on Combined Select' page 2-500 in Informix Guide to Syntax 7.3 You can probably use the following SQL which should produce similar result (cost not verified when compared to the multiple subquery option) select tabname from systables t where not exists ( select * from sysviolations v where t.tabid = v.viotid or t.tabid = v.diatid ) ..mk "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