RE: union subqueries in "from" clause (IDS 9.4)

your query may work in oracle and sqlserver, for informix try this...... select q, sum(v) from TABLE(MULTISET(select q,v from utest where w='a' union all select q,v from utest where w='b')) group by q; -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]On Behalf Of Dan Sent: Friday, May 13, 2005 9:35 AM To: informix-list@iiug.org Subject: union subqueries in "from" clause (IDS 9.4) Hi. The SQL Syntax Guide claims that the subject queries are supported. (Ref: http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.sqls.doc/sqls697.htm) E.g. for a simple table like... CREATE TABLE utest ( q INTEGER NOT NULL, w CHAR(1) NOT NULL, v INTEGER NOT NULL, PRIMARY KEY (q,w) ); ...I would expect that the following would work select q, sum(v) from (select q,v from utest where w='a' union all select q,v from utest where w='b') group by q; But instead it produces a "syntax error" pointing to the 2nd "v" (from the left, column 32). Any ideas on why I can't get this to work? sending to informix-list