Re: Common Table Expression
On May 29, 3:10 pm, "idsexp...@gmail.com" <idsexp...@gmail.com> wrote:
> 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.
That is what I thought he meant but I am not that fond of acronyms
especially when they vary between products.