By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,018 Members | 901 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,018 IT Pros & Developers. It's quick & easy.

common table expression vs. nested table expression

P: n/a
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_transactions
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_transactions
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_transactions
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_transactions
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
Mar 7 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Mar 6, 5:05 pm, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
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_transactions
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_transactions
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_transactions
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_transactions
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

Mar 7 '07 #2

P: n/a
jefftyzzer wrote:
On Mar 6, 5:05 pm, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
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_transactions
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_transactions
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_transactions
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_transactions
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
Mar 7 '07 #3

P: n/a
On 3/6/2007 at 8:52 PM, in message <55*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:
jefftyzzer wrote:
>On Mar 6, 5:05 pm, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
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_transactions
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_transactions
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_transactions
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_transactions
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
Mar 7 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.