Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Decode and ranges?
JCEdleman wrote
>Psydo -code:
>Select gender_cd,
>count(decode(round(months_between(birth_dt,sysdate)/12),between 6 and 10,
total
>count))) "Age 6-10"
>from members
>where . . . . .
What about a user-defined function?
create or replace function inAgeGroup
( pBirthDate in date , pLowAge in integer , pHighAge in integer ) return integer
if round( months_between(pBirthDate, sysdate) / 12 ) between nvl(pLowAge, 0) and nvl(pHighAge, 200) then return 1; else return 0; end if;
select gender_cd " "
, sum( inAgeGroup(birth_dt, null, 5) ) "Age under 6" , sum( inAgeGroup(birth_dt, 6, 10) ) "Age 6-10" , sum( inAgeGroup(birth_dt, 11, 15) ) "Age 11-15" , sum( inAgeGroup(birth_dt, 16, null) ) "Age over 15" , sum(1) "Total"
Arjan. Received on Sun Feb 28 1999 - 15:25:52 CST