common table expression vs. nested table expression

[Frank Swarbrick]

I was just messing around trying to learn things and attempted the
following:

select brch_nbr
, sum(case when post_flag != 'P' then amount else 0 end) as sum_amount
from film.film_trans actions
group by brch_nbr
having sum_amount 0
order by sum_amount desc
;

This didn't work because sum_amount is not yet defined (or whatever) at the
time that the having clause is evaluated. So then I did this:

Example 1)
select brch_nbr
, sum(case when post_flag != 'P' then amount else 0 end) as sum_amount
from film.film_trans actions
group by brch_nbr
having sum(case when post_flag != 'P' then amount else 0 end) 0
order by sum_amount desc
;

This worked, but I didn't like it because if I changed how sum_amount is
calculated I'd have to make a change in two places.
So then I changed it to use a nested table expression thusly:

Example 2)
select brch_nbr
, sum_amount
from (
select brch_nbr
, sum(case when post_flag = 'P' then 0 else amount end) as
sum_amount
from film.film_trans actions
group by brch_nbr
) as ft
where sum_amount 0
order by sum_amount desc
;

This worked fine and in fact gave me the same 'cost' as Example 1.
Then I thought I might make it more readible, to me, and changed it to use a
common table expression instead:

Example 3)
with
ft as (
select brch_nbr
, sum(case when post_flag = 'P' then 0 else amount end) as
sum_amount
from film.film_trans actions
group by brch_nbr
)
select brch_nbr
, sum_amount
from ft
where sum_amount 0
order by sum_amount desc
;

This also gave me the same 'cost' as the other two.
Other than the 'readibility' factor, which I'm assuming is just a matter of
personal preference, is there any other reason I'd chose a CTE over an NTE,
or NTE over CTE?
I like the CTE, personally, because it better segregates the definition of
the temporary table itself and the select of the data from that table.

Thanks,
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

[jefftyzzer]

On Mar 6, 5:05 pm, "Frank Swarbrick" <Frank.Swarbr.. [e-mail] m>
wrote:

I was just messing around trying to learn things and attempted the
following:

select brch_nbr
, sum(case when post_flag != 'P' then amount else 0 end) as sum_amount
from film.film_trans actions
group by brch_nbr
having sum_amount 0
order by sum_amount desc
;

This didn't work because sum_amount is not yet defined (or whatever) at the
time that the having clause is evaluated. So then I did this:

Example 1)
select brch_nbr
, sum(case when post_flag != 'P' then amount else 0 end) as sum_amount
from film.film_trans actions
group by brch_nbr
having sum(case when post_flag != 'P' then amount else 0 end) 0
order by sum_amount desc
;

This worked, but I didn't like it because if I changed how sum_amount is
calculated I'd have to make a change in two places.
So then I changed it to use a nested table expression thusly:

Example 2)
select brch_nbr
, sum_amount
from (
select brch_nbr
, sum(case when post_flag = 'P' then 0 else amount end) as
sum_amount
from film.film_trans actions
group by brch_nbr
) as ft
where sum_amount 0
order by sum_amount desc
;

This worked fine and in fact gave me the same 'cost' as Example 1.
Then I thought I might make it more readible, to me, and changed it to use a
common table expression instead:

Example 3)
with
ft as (
select brch_nbr
, sum(case when post_flag = 'P' then 0 else amount end) as
sum_amount
from film.film_trans actions
group by brch_nbr
)
select brch_nbr
, sum_amount
from ft
where sum_amount 0
order by sum_amount desc
;

This also gave me the same 'cost' as the other two.
Other than the 'readibility' factor, which I'm assuming is just a matter of
personal preference, is there any other reason I'd chose a CTE over an NTE,
or NTE over CTE?
I like the CTE, personally, because it better segregates the definition of
the temporary table itself and the select of the data from that table.

Thanks,
Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

Hi, Frank

Yes--I think the readability, modularity, reusability, and reduction
in query size afforded by the use of CTEs is compelling. AFAIK, if you
don't reuse your CTE in the query, DB2 treats it just like a nested
table (which is probably why your numbers are consistent), whereas if
you refer to it more than once, through, e.g., an alias, DB2 may
create a TEMP table for it.

Regards,

--Jeff

[Serge Rielau]

jefftyzzer wrote:

On Mar 6, 5:05 pm, "Frank Swarbrick" <Frank.Swarbr.. [e-mail] m>
wrote:

>I was just messing around trying to learn things and attempted the
following:

select brch_nbr
, sum(case when post_flag != 'P' then amount else 0 end) as sum_amount
from film.film_trans actions
group by brch_nbr
having sum_amount 0
order by sum_amount desc
;

This didn't work because sum_amount is not yet defined (or whatever) at the
time that the having clause is evaluated. So then I did this:

Example 1)
select brch_nbr
, sum(case when post_flag != 'P' then amount else 0 end) as sum_amount
from film.film_trans actions
group by brch_nbr
having sum(case when post_flag != 'P' then amount else 0 end) 0
order by sum_amount desc
;

This worked, but I didn't like it because if I changed how sum_amount is
calculated I'd have to make a change in two places.
So then I changed it to use a nested table expression thusly:

Example 2)
select brch_nbr
, sum_amount
from (
select brch_nbr
, sum(case when post_flag = 'P' then 0 else amount end) as
sum_amount
from film.film_trans actions
group by brch_nbr
) as ft
where sum_amount 0
order by sum_amount desc
;

This worked fine and in fact gave me the same 'cost' as Example 1.
Then I thought I might make it more readible, to me, and changed it to use a
common table expression instead:

Example 3)
with
ft as (
select brch_nbr
, sum(case when post_flag = 'P' then 0 else amount end) as
sum_amount
from film.film_trans actions
group by brch_nbr
)
select brch_nbr
, sum_amount
from ft
where sum_amount 0
order by sum_amount desc
;

This also gave me the same 'cost' as the other two.
Other than the 'readibility' factor, which I'm assuming is just a matter of
personal preference, is there any other reason I'd chose a CTE over an NTE,
or NTE over CTE?
I like the CTE, personally, because it better segregates the definition of
the temporary table itself and the select of the data from that table.

Thanks,
Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

Hi, Frank

Yes--I think the readability, modularity, reusability, and reduction
in query size afforded by the use of CTEs is compelling. AFAIK, if you
don't reuse your CTE in the query, DB2 treats it just like a nested
table (which is probably why your numbers are consistent), whereas if
you refer to it more than once, through, e.g., an alias, DB2 may
create a TEMP table for it.

Correct. In fact if you use it more than once DB2 will start of with a
temp and the optimizer may later decide to "break the CSE" if it deems
it semantically safe.

Interesting side-note:
A HAVING clause is nothing else than a short form for a WHERE clause
over the GROUP BY of a nested query. So all three queries are identical.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Frank Swarbrick]

On 3/6/2007 at 8:52 PM, in message <55************ *@mid.individua l.net>,
Serge Rielau<sr*****@ ca.ibm.comwrote :

jefftyzzer wrote:

>On Mar 6, 5:05 pm, "Frank Swarbrick" <Frank.Swarbr.. [e-mail] m>
wrote:

>>I was just messing around trying to learn things and attempted the
following:

select brch_nbr
, sum(case when post_flag != 'P' then amount else 0 end) as

sum_amount

>> from film.film_trans actions
group by brch_nbr
having sum_amount 0
order by sum_amount desc
;

This didn't work because sum_amount is not yet defined (or whatever) at

the

>>time that the having clause is evaluated. So then I did this:

Example 1)
select brch_nbr
, sum(case when post_flag != 'P' then amount else 0 end) as

sum_amount

>> from film.film_trans actions
group by brch_nbr
having sum(case when post_flag != 'P' then amount else 0 end) 0
order by sum_amount desc
;

This worked, but I didn't like it because if I changed how sum_amount

is

>>calculated I'd have to make a change in two places.
So then I changed it to use a nested table expression thusly:

Example 2)
select brch_nbr
, sum_amount
from (
select brch_nbr
, sum(case when post_flag = 'P' then 0 else amount end) as
sum_amount
from film.film_trans actions
group by brch_nbr
) as ft
where sum_amount 0
order by sum_amount desc
;

This worked fine and in fact gave me the same 'cost' as Example 1.
Then I thought I might make it more readible, to me, and changed it to

use a

>>common table expression instead:

Example 3)
with
ft as (
select brch_nbr
, sum(case when post_flag = 'P' then 0 else amount end) as
sum_amount
from film.film_trans actions
group by brch_nbr
)
select brch_nbr
, sum_amount
from ft
where sum_amount 0
order by sum_amount desc
;

This also gave me the same 'cost' as the other two.
Other than the 'readibility' factor, which I'm assuming is just a matter

of

>>personal preference, is there any other reason I'd chose a CTE over an

NTE,

>>or NTE over CTE?
I like the CTE, personally, because it better segregates the definition

of

>>the temporary table itself and the select of the data from that table.

Hi, Frank

Yes--I think the readability, modularity, reusability, and reduction
in query size afforded by the use of CTEs is compelling. AFAIK, if you
don't reuse your CTE in the query, DB2 treats it just like a nested
table (which is probably why your numbers are consistent), whereas if
you refer to it more than once, through, e.g., an alias, DB2 may
create a TEMP table for it.

Correct. In fact if you use it more than once DB2 will start of with a
temp and the optimizer may later decide to "break the CSE" if it deems
it semantically safe.

Interesting side-note:
A HAVING clause is nothing else than a short form for a WHERE clause
over the GROUP BY of a nested query. So all three queries are identical.

Cool. I'm glad. Thanks for the information!

Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA