dynamic sum decode

[cli]

Hi,

I would appreciate any help with this please !

I am trying to make the sql below more dynamic

SELECT TB_NAT.CL_NAT,
SUM(DECODE(TB_BAT.DE_STATUS,'JR',1,0)) AS JR,
SUM(DECODE(TB_BAT.CL_GENDER_CODE,1,1,0)) MALE,
SUM(DECODE(TB_BAT.CL_GENDER_CODE,2,1,0)) FEMALE
FROM tb_BAT, TB_NAT
WHERE TB_NAT.CL_NAT= tb_BAT.CL_NAT
GROUP BY TB_NAT.CL_NAT, TB_BAT.DE_STATUS


my problem is the second row, what i want is for each different DE_STATUS on TB_BAT for the sum decode to count it as one and then name the column after the status, this way if a new status is added a new column will come back with a count and called by the new status name, is there any way you can loop through the sum decode in this way ?

thanks .. i hope i explined this OK

Cli

 

[tdatgod]

Hi,
is DECODE an ANSI Syntax or Oracle Specific? If this is something Specific to Oracle you might want to post your questions in one of the many Oracle Forums on Tek Tips.