Re: SELECT syntax with alias
On Wed, 3 Feb 1999 rottem@veon.com wrote:
> In Oracle I can do the following:
>
> SELECT a.k, a.l, b.x, b.y from blabla a,
> (select x,y from z wher x>5) b;>
> How can I use a subquery's result as a table in Informix?
That is SQL-92 syntax. AFAIK, it isn't supported in any version
of Informix, though I may simply have been using the wrong syntax
when I checked casually on 7.30.
Therefore, you have to use the Informix idiom:
SELECT x, y FROM z WHERE x > 5 INTO TEMP b;
SELECT a.k, a.l, b.x, b.y FROM blabla a, b;
DROP TABLE b;
This corresponds to what you wrote -- it gives the cartesian
product of the two tables. There would probably be a WHERE
clause on the second SELECT.
[...extended SQL removed...]
Yours,
Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h>
Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN
Informix IDN for D4GL & Linux -- http://www.informix.com/idn
Jonathan Leffler (jleffler@informix.com) wrote:
:
: On Wed, 3 Feb 1999 rottem@veon.com wrote:
: > In Oracle I can do the following:
: >
: > SELECT a.k, a.l, b.x, b.y from blabla a,
: > (select x,y from z wher x>5) b;
: >
: > How can I use a subquery's result as a table in Informix?
:
: That is SQL-92 syntax. AFAIK, it isn't supported in any version
: of Informix, though I may simply have been using the wrong syntax
: when I checked casually on 7.30.
All things are solved in the next release ;-)
IDS/UD 9.2
CREATE TABLE Foo (
A INTEGER NOT NULL,
B INTEGER NOT NULL
);--
INSERT INTO Foo VALUES ( 1, 1 );
INSERT INTO Foo VALUES ( 1, 2 );
INSERT INTO Foo VALUES ( 1, 3 );
INSERT INTO Foo VALUES ( 1, 4 );
INSERT INTO Foo VALUES ( 1, 5 );
INSERT INTO Foo VALUES ( 2, 6 );
INSERT INTO Foo VALUES ( 2, 7 );
INSERT INTO Foo VALUES ( 2, 8 );
INSERT INTO Foo VALUES ( 2, 9 );
INSERT INTO Foo VALUES ( 2, 10 );--
INSERT INTO Foo VALUES ( 3, 1 );
INSERT INTO Foo VALUES ( 3, 2 );
INSERT INTO Foo VALUES ( 3, 3 );
INSERT INTO Foo VALUES ( 3, 4 );
INSERT INTO Foo VALUES ( 3, 5 );
INSERT INTO Foo VALUES ( 4, 6 );
INSERT INTO Foo VALUES ( 4, 7 );
INSERT INTO Foo VALUES ( 4, 8 );
INSERT INTO Foo VALUES ( 4, 9 );
INSERT INTO Foo VALUES ( 4, 10 );--
SELECT W.A
FROM TABLE(MULTISET(SELECT A AS A, SUM(B) AS B
FROM Foo
GROUP BY A HAVING SUM(B) > 30)) W;--
-- RESULT ->
--
-- a
--
-- 2
-- 4
--
--
CREATE TABLE Bar (
A INTEGER NOT NULL,
C VARCHAR(32) NOT NULL
);--
INSERT INTO Bar VALUES ( 1, 'First');
INSERT INTO Bar VALUES ( 2, 'Second');
INSERT INTO Bar VALUES ( 3, 'Third');
INSERT INTO Bar VALUES ( 4, 'Fourth');--
--
SELECT B.C
FROM Bar B,
TABLE(MULTISET(SELECT A AS A, SUM(B) AS B
FROM Foo
GROUP BY A HAVING SUM(B) > 30)) W
WHERE W.A = B.A;--
-- RESULT ->
--
-- c
--
-- Second
-- Fourth
--
The TABLE(MULTISET()) style of sytnax is a bit crappy IMHO,
but it does provide a more general model for handling things
like COLLECTIONS and so on.
KR
Pb