Using >= in a decode statement

[nuct]

Ive got this bit of code:

SUM(DECODE(AE.AE_CHOICE_NO,7,1,0)) "CLEARING"

That needs to be changed so that the critera is 7 or greater rather than just 7.

Thanks,

Simon.

Merry Christmas

 

[rac2]

SUM(DECODE(AE.AE_CHOICE_NO,
           1,0,
           2,0,
           3,0,
           4,0,
           5,0,
           6,0,
             1)) "CLEARING"

- or -

SUM(CASE
       WHEN AE.AE_CHOICE_NO >=  7 THEN 1
       ELSE 0)
    END ) AS "CLEARING"

 

[crufty]

even easier:

sum( decode( sign( AE.AE_CHOICE_NO - 7 ), -1, 0, 0, 1, 1, 1 ) ) )

This will give you a sum for all AE.AE_CHOICE_NO >= 7.

 

[nuct]

Cheers for that,

Simon.