SEO

#1 (**permalink**) 02-29-2008, 02:21 AM

What is the SQL Server equivalent of DB2 common table expressions? For
example,

with gry(year,count) as(
select floor(sem/10),count(distinct ssn)
from grades
group by floor(sem/10)
)
select year,sum(count) Head_Count from gry
group by year
having year >= 1980;

N. Shamsundar
University of Houston

#2 (**permalink**) 02-29-2008, 02:21 AM

"N. Shamsundar" <shamsundar_AT_uh.edu@nospam.xyz> wrote in message
news:c4npes$3ecc$1@masala.cc.uh.edu...
> What is the SQL Server equivalent of DB2 common table expressions? For
> example,
>
> with gry(year,count) as(
> select floor(sem/10),count(distinct ssn)
> from grades
> group by floor(sem/10)
> )
> select year,sum(count) Head_Count from gry
> group by year
> having year >= 1980;
>
> N. Shamsundar
> University of Houston
>

If you have a lot of queries which will reference the CTE, you could create
a view or table-valued function. If you only have a few queries, or if you
can't create a view or function for some reason, then a derived table is
probably the only other alternative. Your example seems to be relatively
simple, so I guess any of these options will work, but in more complex cases
a function might give you the most flexibility. CTEs will be in Yukon, by
the way.

Simon

#3 (**permalink**) 02-29-2008, 02:21 AM

something like this:

*/ Untested! */
select
a.year,
a.sum(amount)

from
(select floor(sem/10) as year ,count(distinct ssn) as amount
from grades
group by floor(sem/10)
) as a

where
year >=1980

group by a.year

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Common Table Expression?