438,018 Members | 901 Online 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
3 Replies

 P: n/a On Mar 6, 5:05 pm, "Frank Swarbrick" 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" wrote: >I was just messing around trying to learn things and attempted thefollowing: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 thetime 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 iscalculated 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) assum_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 acommon table expression instead:Example 3)with ft as ( select brch_nbr , sum(case when post_flag = 'P' then 0 else amount end) assum_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 ofpersonal 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 ofthe temporary table itself and the select of the data from that table.Thanks,Frank---Frank SwarbrickSenior Developer/Analyst - Mainframe ApplicationsFirstBank 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 RielauOn Mar 6, 5:05 pm, "Frank Swarbrick" wrote: >>I was just messing around trying to learn things and attempted thefollowing: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) assum_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) assum_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, FrankYes--I think the readability, modularity, reusability, and reductionin query size afforded by the use of CTEs is compelling. AFAIK, if youdon't reuse your CTE in the query, DB2 treats it just like a nestedtable (which is probably why your numbers are consistent), whereas ifyou refer to it more than once, through, e.g., an alias, DB2 maycreate 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. 