How to use Decode function

From: <poojareddys_at_gmail.com>
Date: 25 Feb 2006 05:36:17 -0800
Message-ID: <1140874577.452113.229500_at_z34g2000cwc.googlegroups.com>

Hi,

Can anyone help me how to use DECODE function instead of CASE statement in the following query.

select a.ASSOC_NAME region,

                                    count(CASE   WHEN va.ANSWER <>
'CSS' or va.ANSWER <> 'RS' THEN 1 end) "Already_Treatment",
                                    count(CASE   WHEN va.ANSWER = 'CSS'
THEN 1 end) "  Treatment_CSS",
                                    count(CASE   WHEN va.ANSWER <> 'RS'
THEN 1 end)  " Treatment_RS",
                                    count(CASE   WHEN va.ANSWER = 'CSS'

and va.ANSWER = 'RS' THEN 1 end) " Treatment_CSS_RS"

from v_assessment_answer va,people_x_association pxa,association a where va.INS_ID = 1969 -- Instrument ID hardcoded for instrument MH Enrollment And Certification CSS RS.

and va.DATE_END is null
and va.DATE_START between :p_from_date and :p_to_date
and va.INS_QUES_GRID_NAME= 'IF CONSUMER NEW TO SERVICE:'
and va.PEO_ID = pxa.PEO_ID
and pxa.ASSOC_ID = decode(:p_region, 0, pxa.ASSOC_ID, :p_region)
and pxa.END_DATE is null

and pxa.ASSOC_ID in (100,102,182,183,184,185) and pxa.ASSOC_ID = a.ASSOC_ID
group by a.ASSOC_NAME

Thanks,
Pooja Received on Sat Feb 25 2006 - 14:36:17 CET