union subqueries in "from" clause (IDS 9.4)
Dan found that IDS 9.4 rejects a derived-table subquery in the FROM clause (e.g. SELECT q, SUM(v) FROM (SELECT q,v FROM utest ... UNION ALL ...) GROUP BY q), giving a syntax error; even SELECT * FROM (SELECT * FROM utest) fails. Jonathan Leffler confirmed IDS simply lacked sub-queries in the FROM clause, despite the docs suggesting otherwise. The working substitute, which Dan verified, is the collection-derived-table syntax: FROM TABLE(MULTISET(SELECT ...)), with the UNION placed inside and an AS clause added if column names are needed — not elegant or especially efficient, but functional.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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?
the FROM clause .... http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.sqls.doc/sqls697.htm No mention of a subselect as being a suitable candidate to a FROM clause. Maybe a doc error in the UNION description
this fails too
select * from (select * from utest)
A couple of offline responses suggested something that appears to work:
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;
Thanks for your help!
"Dan" <bubba@dekworld.com> wrote in message
news:9W2he.12498$NZ1.1857@fe09.lga...
> 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)
>
> ...
> 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).
>
Dan wrote:
> 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?
Roughly the same reason that this won't work either:
SELECT q, SUM(v) FROM (SELECT q,v FROM utest WHERE w = 'a') GROUP BY q;
Yes; it is an irksome (even embarrassing) omission - it was one of the
features of SQL 1992 and it is implemented by essentially every other
SQL DBMS on the planet.
The feature in question is 'Sub-queries in FROM clause of SELECT statement'.
The 'workaround' is:
SELECT q, SUM(v) FROM TABLE(MULTISET(SELECT q,v FROM utest WHERE w ='a')) GROUP BY q;
You might need an AS clause to designate the column names. I make no
pretence that it is elegant or efficient - it is certainly not elegant
and usually isn't efficient either. You should be able to put the UNION
inside that notation.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/
Thanks again to everyone who suggested the Table-Multiset solution to this problem. It's something I don't think I'd ever have figured out on my own. And, after spending some time reading the docs on multisets & virtual tables & collection subqueries & collection-derived tables, I still find it a bit confusing... For my purposes (selecting from the results of a subquery), it seems like quite a lot of semantic structure for something that's otherwise fairly simple. But it works, which is the important part. And maybe after another read or two it will make more sense.