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. These can also be done with real views and it makes things easier to read. I'll bet his query could be written without these CTE's and maybe without views. I have mostly seen these inline views misused. So what are you actually trying to do?

bozon wrote: > These can also be done with real views and it makes things easier to > read. I'll bet his query could be written without these CTE's and > maybe without views. I have mostly seen these inline views misused. > > So what are you actually trying to do? Couple of clarification. CTE (Common table expression) is a SQL Standard term. CTe's have three usages: * They are used to make nested subqueries easier to read by introducing the individual pieces of the query one at a time. That, of course, is merely semantic sugar. So no functional difference to IDS 11 subqueries. * CTE are used to share the resultset of a nested query in multiple places (thats where the "common" comes from. The difference to a temp table is that the optimizer can see teh complete query, push down predicates, replicate the query or do all sorts of other stuff. Finally there is no need to cleanup. The temp if needed, goes away at the end of the statement. * CTE are used as teh foundation fro recusrion in teh SQL Standard So the answer of equivalent features, as always is: It depends :-) Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

On May 29, 4:41 pm, Serge Rielau <srie...@ca.ibm.com> wrote: > bozon wrote: > > These can also be done with real views and it makes things easier to > > read. I'll bet his query could be written without these CTE's and > > maybe without views. I have mostly seen these inline views misused. > > > So what are you actually trying to do? > > Couple of clarification. CTE (Common table expression) is a SQL Standard > term. CTe's have three usages: > * They are used to make nested subqueries easier to read by introducing > the individual pieces of the query one at a time. > That, of course, is merely semantic sugar. > So no functional difference to IDS 11 subqueries. > > * CTE are used to share the resultset of a nested query in multiple > places (thats where the "common" comes from. > The difference to a temp table is that the optimizer can see teh > complete query, push down predicates, replicate the query or do all > sorts of other stuff. Finally there is no need to cleanup. The temp if > needed, goes away at the end of the statement. > > * CTE are used as teh foundation fro recusrion in teh SQL Standard I saw a presentation on the recursion very interesting to say the least. I haven't really had a chance to play with it. I am going to have to push harder to go to 11. > > So the answer of equivalent features, as always is: It depends :-) > > Cheers > Serge > > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab I have just seen it used so badly I have even seen basically the following: select * from (select * from dog) as cat no, really I have just search some of the posts on IDS that I have responded to about this. OK, here is one: http://groups.google.com/group/comp.databases.informix/browse_thread/thread/64ca8b96ae1fccb1/75d43822598534f2?hl=en&lnk=gst&q=bozon+in-line+views#75d43822598534f2 I makes me an even grumpier old man and no one needs that.

Thank you all for your valuable comments. Good to know that IDS 11 supports subquery syntax in the FROM clause. We are at Ver 7.x and I think we should upgrade it to a higher version. - Murty