Re: porting complex outer join, sub-query
Thanks a lot of for you help! This is very comprehensive!
I believe the "into tmp" clause will work for me.
Thanks,
Thomas
----- Original Message -----
From: "Paul Brown" <paul.NOSPAM.brown@informix.com>
To: <informix-list@iiug.org>
Sent: Friday, September 08, 2000 11:38 AM
Subject: Re: porting complex outer join, sub-query
>
>
> 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 U