Re: AW: Sql muddle

On 03-Apr-2012 06:32 , Joe Pluta wrote:

On 4/3/2012 8:29 AM, rob@xxxxxxxxx wrote:

An alternative work around is to take this:

select
client, min(childAge) as youngest, max(childAge) as eldest
from table group by client
having youngest< 20 and eldest> 20

and convert it to

With T1 as
( select
client, min(childAge) as youngest, max(childAge) as eldest
from table group by client
)
Select client, youngest, eldest from T1
where youngest< 20 and eldest> 20

either should work - your choice.

Agreed. That's what I usually do when my grouping requirements start
to get complex.

I love CTEs. I use them even when I don't strictly have to, just to
break apart a complex query so that I can debug it each step of the
way.

FWiW: When any particular CTE has value outside a specific query request, creating and encapsulating that query as a VIEW is a generally better choice. That is because the VIEW [instead of a CTE] can then be referenced by other queries [whereas a CTE is available only to the one query], including referenced by queries within other VIEWs, and each [underlying] VIEW can be /debugged/ individually much like the results of each CTE.

create view qtemp/cagag (client, youngest, eldest, childcnt) as
(select c client, min(age), max(age), count(*)
from qtemp/cag group by c)
; -- a VIEW returns youngest and eldest in named columns just
-- like the CTE would; COUNT(*) added for more capabilities
create view qtemp/cag2020 (client) as
(select client
from qtemp/cagag
where youngest< 20 and eldest> 20
)
; -- referencing prior VIEW, clients with 1 child over\under 20
create view qtemp/cagGT2cnt (client, kidcount) as
(select client, childcnt
from qtemp/cagag
where childcnt> 2
)
; -- the first VIEW slightly more generic with count included
-- can enable HAVING COUNT(*)>2; further column renaming too


FWiW: As well as the CTE, a NTE can be utilized to name the columns and replace the HAVING clause; e.g. either of the following variations, where one names the columns in the SELECT and the other provides a column list:

select client, youngest, eldest
from (select c client, min(age) youngest, max(age) eldest
from qtemp/cag group by c) as NTE
where youngest<20 and eldest>20

select client, youngest, eldest
from ( select client, min(childAge), max(childAge)
from theTable group by client
) as NTE (client, youngest, eldest)
where youngest< 20 and eldest> 20

I generally prefer the column list variations of the syntax for queries; e.g. for the CTE Rob gave, instead using a column-list:

With T1 (client, youngest, eldest) as
( select client, min(childAge), max(childAge)
from table group by client
)
Select client, youngest, eldest from T1
where youngest< 20 and eldest> 20


Regards, Chuck