Re: Decode and ranges?

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Sun, 28 Feb 1999 22:25:52 +0100
Message-ID: <36d9b46a$0$16767@newton>

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

    as
    begin

        if round( months_between(pBirthDate, sysdate) / 12 )
        between nvl(pLowAge, 0)
        and nvl(pHighAge, 200) then
            return 1;
        else
            return 0;
        end if;

    end inAgeGroup;
    /
    show errors

    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"

    from members
    where ....
    group by gender_cd; -- hmmm, not too sure...

Arjan. Received on Sun Feb 28 1999 - 15:25:52 CST