RE: union subqueries in "from" clause (IDS 9.4)
Posted in 2005
Topics: SQL Development & Query Writing, Versions, Editions & End-of-Life
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