porting complex outer join, sub-query
This is a multi-part message in MIME format.
------=_NextPart_000_0118_01C018F9.AE3F4800
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset="iso-8859-1"
I am trying to port a query similar to the following one from Oracle =
8.04 to Informix Dynamic Server 2000:
select V.x, V.y, X.x, X.y
from (select A.x, B.y from A, B where A.x =3D 'vx' and B.y =3D'vy') V,
(select C.x, C.y from C union all select D.x, D.y from D) X
where V.x =3D X.x (+)
and V.y =3D X.y (+)
I am an Informix novice. The first thing I learned is that sub-queries =
are not allowed in the from clause. However if that's not possible how =
could you formulate this query in Informix?
Any help is appreciated.
Thanks,
Thomas
------=_NextPart_000_0118_01C018F9.AE3F4800
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I am trying to port a query similar to =
the=20
following one from Oracle 8.04 to Informix Dynamic Server =
2000:</FONT></DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>select V.x, V.y, X.x, X.y</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>from (select A.x, B.y from A, =
B where A.x=20
=3D 'vx' and B.y =3D'vy') V,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> =
(select C.x,=20
C.y from C union all select D.x, D.y from D) X</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>
<DIV>where V.x =3D X.x (+)</DIV>
<DIV>and V.y =3D X.y (+)</DIV>
<DIV> </DIV>
<DIV>I am an Informix novice. The first thing I learned is that =
sub-queries=20
are not allowed in the from clause. However if that's not possible =
how=20
could you formulate this query in Informix?</DIV>
<DIV> </DIV>
<DIV>Any help is appreciated.</DIV>
<DIV> </DIV>
<DIV>Thanks,</DIV>
<DIV>Thomas</DIV>
<DIV> </DIV></FONT></DIV></DIV></BODY></HTML>
------=_NextPart_000_0118_01C018F9.AE3F4800--
Thomas Burkhardt wrote:
I am trying to port a query similar to the following one from Oracle =
> 8.04 to Informix Dynamic Server 2000:
> select V.x, V.y, X.x, X.y
> from (select A.x, B.y from A, B where A.x =3D 'vx' and B.y =3D'vy') V,
> (select C.x, C.y from C union all select D.x, D.y from D) X
> where V.x =3D X.x (+)
> and V.y =3D X.y (+)
--
-- So you wanna do this then?
--
-- select V.x, V.y, X.x, X.y
-- from (select A.x, B.y from A, B where A.x =3D 'vx' and B.y =3D'vy') V,
-- (select C.x, C.y from C union all select D.x, D.y from D) X
-- where V.x =3D X.x (+)
-- and V.y =3D X.y (+)
--
--
-- Well first, I have no idea what (+) means. I guess that it's some
-- kind of hack around optimizer issues (I bet this thing materializes
-- the results of the inners before doing the join.)
--
-- BTW: Quel version (from memory; no Quel processor handy)
--
-- range of V is ( retrieve ( x, y ) from A, B where x = "vx" and y = "vy" );
-- range of U is ( retrieve ( x, y ) from C, retrieve x, y FROM D );
-- retrieve ( V.x, V.y, U.x, U.y ) from U, V where V.x = U.x and V.y = U.y;
--
-- But I digress. . .
--
CREATE TABLE A (
Id SERIAL PRIMARY KEY,
X CHAR(2) NOT NULL
);--
INSERT INTO A ( X )
SELECT T.V
FROM TABLE(SET{'xx','xy','xz','yx','yy','yz','zx','zy','zz'}) T ( V );--
CREATE TABLE B (
Id SERIAL PRIMARY KEY,
Y CHAR(2) NOT NULL
);--
INSERT INTO B ( Y )
SELECT T.V
FROM TABLE(SET{'xx','xy','xz','yx','yy','yz','zx','zy','zz'}) T ( V );--
CREATE TABLE C (
Id SERIAL PRIMARY KEY,
X CHAR(2) NOT NULL,
Y CHAR(2) NOT NULL
);--
INSERT INTO C ( X, Y )SELECT T1.V || T2.V, T3.V || T4.V
FROM TABLE(SET{'w','x','y','z'}) T1 ( V ),
TABLE(SET{'w','x','y','z'}) T2 ( V ),
TABLE(SET{'w','x','y','z'}) T3 ( V ),
TABLE(SET{'w','x','y','z'}) T4 ( V );
--
CREATE TABLE D (
Id SERIAL PRIMARY KEY,
X CHAR(2) NOT NULL,
Y CHAR(2) NOT NULL
);--
INSERT INTO C ( X, Y )SELECT T1.V || T2.V, T3.V || T4.V
FROM TABLE(SET{'w','x','y','z'}) T1 ( V ),
TABLE(SET{'w','x','y','z'}) T2 ( V ),
TABLE(SET{'w','x','y','z'}) T3 ( V ),
TABLE(SET{'w','x','y','z'}) T4 ( V );
--
-- Now, in IDS.2000, there is a variant of the "sub-query" stuff
-- you mention. (It might be better to refer to this is a "nested" or
-- query or perhaps an "enclosed" query to differentiate it from the
-- cases where you're doing a quantifier style sub-query; an IN,
-- EXISTS or ALL). But it's a bit weak. It doesn't do the UNION ALL
-- and it's mechanism for handling the naming of nested columns isn't
-- good.
--
-- Instead, use temporary tables.
--
--
BEGIN WORK;
--
SELECT A.x, B.y
FROM A, B
WHERE A.x = 'xx'
AND B.y = 'zz'
INTO TEMP Bar;--
SELECT C.X, C.Y
FROM C
INTO TEMP Foo;--
-- NOTE: Your query specifies UNION ALL. If you want to use a
-- UNION (discard duplicates) then you need to do something a bit
-- fancier here.
--
INSERT INTO Foo
SELECT D.X, D.Y
FROM D;--
SELECT B.X, B.Y, F.X, F.Y
FROM Bar B, Foo F
WHERE B.X = F.X
AND B.Y = F.Y;--
COMMIT WORK;
--
-- Note that the temporary tables (Foo and Bar in this little
-- example) will be cleaned up automatically by the DBMS when your
-- current session DISCONNECTs. But if you're doing this operation
-- several times in a single session, then you might want to pop the
-- table maintenance into a stored procedure, as follows:
--
CREATE PROCEDURE Reset_Temp_Table_Foo()
ON EXCEPTION IN (-206)
CREATE TEMP TABLE Foo
(
X CHAR(2) NOT NULL,
Y CHAR(2) NOT NULL
);
END EXCEPTION;
--
-- NOTE: What follows is not a typo. Run DROP twice to ensure that
-- the CREATE happens at least once. What is going on here
-- is that the block between the ON EXCEPTION and the END
-- EXCEPTION does not run UNLESS the exception (-206) (which
-- is "Table not found) is thrown.
--
-- Consider the following cases:
--
-- 1. If the table exists, then the exception is not thrown the
-- first DROP TABLE. But it is thrown the second time the
-- DROP is attempted (becase the DROP immediately before it
-- did drop the table.)
--
-- 2. If the table does not exist, or if it was dropped by the
-- previous line, then the first of these two DROP statements will
-- cause the DBMS to throw a -206, in which case the
-- exception handler kicks in and creates the table. In this
-- example, control is passed back to whatever called the
-- PROCEDURE, and the rest of the PROCEDURE body is ignored.
--
DROP TABLE Foo; -- THESE TWO DROP TABLE ARE NOT
DROP TABLE Foo; -- A TYPO. CAREFUL WITH THE yy/pp!
--
-- NOT REACHED
--
END PROCEDURE;--
GRANT EXECUTE ON PROCEDURE Reset_Temp_Table_Foo() TO PUBLIC;--
CREATE PROCEDURE Reset_Temp_Table_Bar()
ON EXCEPTION IN (-206)
CREATE TEMP TABLE Bar
(
X CHAR(2) NOT NULL,
Y CHAR(2) NOT NULL
);
END EXCEPTION;
--
-- NOTE: This is not a typo. Run the DROP Twice to ensure that
-- the CREATE happens.
--
DROP TABLE Bar; -- THESE TWO DROP TABLE ARE NOT
DROP TABLE Bar; -- A TYPO. CAREFUL WITH THE yy/pp!
--
-- NOT REACHED
--
END PROCEDURE;--
GRANT EXECUTE ON PROCEDURE Reset_Temp_Table_Foo() TO PUBLIC;--
-- OK. Let's take this baby out for a run.
--
-- First, let's drop the tables. This is meant to be a
-- clean-room test.
--
DROP TABLE Foo;
DROP TABLE Bar;--
-- Now, execute the Reset() procedures. There are no tables, so
-- the exception gets raised immediately, and the temporary tables
-- get created. Recall that TEMP tables are private to the use
-- of a particular session.
--
EXECUTE PROCEDURE Reset_Temp_Table_Foo();
EXECUTE PROCEDURE Reset_Temp_Table_Bar();--
-- Now. Run a little work. Same as before.
--
BEGIN WORK;
--
INSERT INTO Bar
SELECT A.x, B.y
FROM A, B
WHERE A.x = 'xx'
AND B.y = 'zz';--
INSERT INTO Foo
SELECT C.X, C.Y
FROM C;--
-- NOTE: Your query specifies UNION ALL. If you want to use a
-- UNION (discard duplicates) then you need to do something a bit
-- fancier here.
--
INSERT INTO Foo
SELECT D.X, D.Y
FROM D;--
SELECT B.X, B.Y, F.X, F.Y
FROM Bar B, Foo F
WHERE B.X = F.X
AND B.Y = F.Y;--
COMMIT WORK;
--
-- What's in Bar?
--
SELECT * FROM Bar;--
-- Repeat.
--
-- Reset the tables. This time, the tables exist, so the exception
-- is not thrown until the second DROP TABLE.
--
EXECUTE PROCEDURE Reset_Temp_Table_Foo();
EXECUTE PROCEDURE Reset_Temp_Table_Bar();--
-- This execute had the effect of dropping and re-creating the
-- temp table. In other words, it was truncated.
--
BEGIN WORK;
--
INSERT INTO Bar
SELECT A.x, B.
Thomas Burkhardt wrote:
> I am trying to port a query similar to the following one from Oracle =
> 8.04 to Informix Dynamic Server 2000:
> select V.x, V.y, X.x, X.y
> from (select A.x, B.y from A, B where A.x =3D 'vx' and B.y =3D'vy') V,
> (select C.x, C.y from C union all select D.x, D.y from D) X
> where V.x =3D X.x (+)
> and V.y =3D X.y (+)>
> I am an Informix novice. The first thing I learned is that sub-queries =
> are not allowed in the from clause. However if that's not possible how =
> could you formulate this query in Informix?
PLEASE DON"T POST HTML/MIME TO THIS NEWSGROUP! It is TEXT ONLY!
OK, I'm an Oracle novice what the *&^#$ does the query do? Sample schema,
sample data, sample output, something?
Art S. Kagel
> Any help is appreciated.
>
> Thanks,
> Thomas