Re: How to use Decode function

poojareddys_at_gmail.com wrote:
> 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
>
I'd leave the CASE in there and forget DECODE -- unless that's a "learning Oracle" homework assignment.

But look at your code: a value of 'RS' in va.ANSWER in the first CASE will be COUNTed as 1 -- despite the intention I infer from the "va.ANSWER <> 'RS'" condition. I suspect you need AND, not OR. Received on Sun Feb 26 2006 - 01:58:57 CET