Question about common table expression and father-child

[KofKof]

Hello guys,

When i use the common-table-expression keyword WITH, where does the defined data reside? or DB2 just replaces the WITH table with the sql?
for example

with mytable as (select * from sysibm)
Select * from mytable

In DB2 v10, is there an easy way (like in oracle) to define father-child reletionship? or it is just a recursive function with the WITH clause.

Thanks alot,

P.S
Anyone encountered -725 sql error while rebinding the DRDA protocol?

[engh]

This is an old question, but I'm going to answer it anyway, because many people still ask themselves the same question today.

A Common Table Expression (CTE) is simply an externalized derived table.

For example, instead of writing :

Code: Select all

select Col1
     , Col2
     , [...]
     , Coln
from (select ColA as Col1
           , ColB as Col2
           , [...]
           , ColX as Coln
      from My_view
     ) as Derived_table

you may write :

Code: Select all

with Derived_table as
    (select ColA as Col1
           , ColB as Col2
           , [...]
           , ColX as Coln
      from My_view
     )
select Col1
     , Col2
     , [...]
     , Coln
from Derived_table

the advantages are :

• the possible mutualization of the derived table when it is used several times in the same query
• the simplification of the writing of the main query

It is also important to know that CTEs are the only solution for writing recursive queries.

Because CTE is only a derived table, there are no specific storage consideration. Data are only stored in the tables and buffered in RAM as usual.

[sergeyken]

CTE from WITH clause can be considered as "local VIEW definition".
It works almost in the same manner as regular VIEW, but it only exists during the query execution where its corresponding WITH clause located.