Re: How to use Decode function

poojareddys@xxxxxxxxx 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.
.

Relevant Pages

  • Re: HTML Templates
    ... Scott P wrote: ... > I want to stay away from the CSS, ... why did you post your query in a newsgroup dedicated ... Prev by Date: ...
    (comp.infosystems.www.authoring.stylesheets)
  • How to use Decode function
    ... Can anyone help me how to use DECODE function instead of CASE statement ... in the following query. ... count(CASE WHEN va.ANSWER = 'CSS' ... Enrollment And Certification CSS RS. ...
    (comp.databases.theory)