Common Table Expression
The poster asked whether Informix supports Common Table Expressions (the SQL "WITH name AS (SELECT ...)" dynamic-view syntax found in Oracle/DB2), and wanted a workaround other than temp tables. After some requests to explain what a CTE is, answers suggested simply joining the underlying tables into one query, or using Informix's equivalent constructs: Collection Derived Tables, SELECT ... FROM TABLE(MULTISET(SELECT ...)) available in IDS 10, and plain derived tables, SELECT ... FROM (SELECT ...), supported from IDS 11, with a pointer to the IDS 11 Infocenter docs.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I think Informix SQL doesn't have CTE feature. Is there any work-around for it ? Thanks Murty
On May 29, 10:54 am, Veeru71 <m_ad...@hotmail.com> wrote: > I think Informix SQL doesn't have CTE feature. > Is there any work-around for it ? > > Thanks > Murty Since we are Informix people could you explain what a CTE is so we could think of the appropriate work around. An example might be nice.
Veeru71 said: > I think Informix SQL doesn't have CTE feature. > Is there any work-around for it ? WTF is CTE? Did you RTFM? See you next Tuesday. -- Bye now, Obnoxio "There were a myriad of problems which conspired to corrupt your reason and rob you of your common sense. Fear got the best of you, and in your panic you turned to the Labour Party. They promised you order, they promised you peace, and all they demanded in return was your silent, obedient consent."
On May 29, 8:54 am, Veeru71 <m_ad...@hotmail.com> wrote: > I think Informix SQL doesn't have CTE feature. > Is there any work-around for it ? > > Thanks > Murty SQL server version of a temp table.
CTE stands for "Common Table Expession". It is kind of a dynamic view (exists in Oracle & DB2) Here is the syntax .... WITH EMP_V (EMP_ID, DEPT_ID, EMP_SAL) AS (select emp_id, dept_id, salary from TAB_A, TAB_B, TAB_C......where.....) DEPT_V (DEPT_ID, LOC) AS (select .....................from ..........) selet EMP_ID, LOC from EMP_V, DEPT_V from EMP_V, DEPT_V where....... In the above exmaple, EMP_V & DEPT_V will be created on the fly by the database server. What is the best way to perform similar thing in Informix ? (Pl. don't tell me to use temp tables). Thanks
Veeru71 said: > CTE stands for "Common Table Expession". It is kind of a dynamic view > (exists in Oracle & DB2) > Here is the syntax .... > > WITH EMP_V (EMP_ID, DEPT_ID, EMP_SAL) AS (select emp_id, dept_id, > salary from TAB_A, TAB_B, TAB_C......where.....) > DEPT_V (DEPT_ID, LOC) AS > (select .....................from ..........) > selet EMP_ID, LOC from EMP_V, DEPT_V > from EMP_V, DEPT_V > where....... > > In the above exmaple, EMP_V & DEPT_V will be created on the fly by > the database server. > What is the best way to perform similar thing in Informix ? (Pl. don't > tell me to use temp tables). What's wrong with a TEMP TABLE, then? -- Bye now, Obnoxio "There were a myriad of problems which conspired to corrupt your reason and rob you of your common sense. Fear got the best of you, and in your panic you turned to the Labour Party. They promised you order, they promised you peace, and all they demanded in return was your silent, obedient consent."
----- Original Message ----- From: "Veeru71" <m_adavi@hotmail.com> Newsgroups: comp.databases.informix To: <informix-list@iiug.org> Sent: Thursday, May 29, 2008 12:52 PM Subject: Re: Common Table Expression > CTE stands for "Common Table Expession". It is kind of a dynamic view > (exists in Oracle & DB2) > Here is the syntax .... > > WITH EMP_V (EMP_ID, DEPT_ID, EMP_SAL) AS (select emp_id, dept_id, > salary from TAB_A, TAB_B, TAB_C......where.....) > DEPT_V (DEPT_ID, LOC) AS > (select .....................from ..........) > selet EMP_ID, LOC from EMP_V, DEPT_V > from EMP_V, DEPT_V > where....... > > In the above exmaple, EMP_V & DEPT_V will be created on the fly by > the database server. > What is the best way to perform similar thing in Informix ? (Pl. don't > tell me to use temp tables). > Thanks > > > > Are you doing something with the CTE that can not be done by just joining all of that mess together? select emp_id, loc from tab_a, tab_b, tab_c, tab_from_loc_a, tab_from_loc_b where join filter and join filter and join filter and ......; Not sure which version if IDS you're on but later versions have this ability select a.emp_id, b.loc from table(multiset( select emp_id, dept_id, salary from tab_a, tab_b, tab_c where ....... )) a, table(multiset( select ...... from ...... where ...... )) b where .......;
On May 29, 11:10 am, "Andrew Ford" <af...@networkip.net> wrote:
> ----- Original Message -----
> From: "Veeru71" <m_ad...@hotmail.com>
>
> Newsgroups: comp.databases.informix
> To: <informix-l...@iiug.org>
> Sent: Thursday, May 29, 2008 12:52 PM
> Subject: Re: Common Table Expression
>
> > CTE stands for "Common Table Expession". It is kind of a dynamic view
> > (exists in Oracle & DB2)
> > Here is the syntax ....
>
> > WITH EMP_V (EMP_ID, DEPT_ID, EMP_SAL) AS (select emp_id, dept_id,
> > salary from TAB_A, TAB_B, TAB_C......where.....)
> > DEPT_V (DEPT_ID, LOC) AS
> > (select .....................from ..........)
> > selet EMP_ID, LOC from EMP_V, DEPT_V
> > from EMP_V, DEPT_V
> > where.......
>
> > In the above exmaple, EMP_V & DEPT_V will be created on the fly by
> > the database server.
> > What is the best way to perform similar thing in Informix ? (Pl. don't
> > tell me to use temp tables).
> > Thanks
>
> Are you doing something with the CTE that can not be done by just joining all of that mess together?
>
> select
> emp_id,
> loc
> from
> tab_a,
> tab_b,
> tab_c,
> tab_from_loc_a,
> tab_from_loc_b
> where
> join filter and
> join filter and
> join filter and
> ......;
>
> Not sure which version if IDS you're on but later versions have this ability
>
> select
> a.emp_id,
> b.loc
> from
> table(multiset(
> select
> emp_id,
> dept_id,
> salary
> from
> tab_a,
> tab_b,
> tab_c
> where
> .......
> )) a,
> table(multiset(
> select
> ......
> from
> ......
> where
> ......
> )) b
> where
> .......;
In IDS v10 we support
SELECT ... FROM TABLE(MULTISET(SELECT .... FROM ...))
which is similar to CTE. In IDS world its called as Collection Derived
Tables (CDT)
Starting with IDS v11 we now support
SELECT ... FROM (SELECT ... FROM ...)
Which is the standard syntax for subqueries in FROM CLAUSE or CTE. In
IDS world this is called Dervied Tables (DT).
Please refer to the IDS v11 infocenter for more details.
Hope this helps.