Oracle CASE expression confusion

Question

Can anyone tell me what is the wrong in the below query?

select case 
         when ANALYSISCODE is null and
              studydomainmdata.studydomainmetadataid > 0
           then 'CD'  
         when ANALYSISCODE is null and
              studydomainmdata.studydomainmetadataid < 0
           then 'CD1'  
         when analysiscode is not null
           then ANALYSISCODE
         else 'N/A'
       end as ANALYSISCODE
from studyanalysis
inner join (slmetadata
              inner join studydomainmdata 
                on slmetadata.slmetadataid = studydomainmdata.slmetadataid and
                   studydomainmdata.studydomainmetadataid=-9)
  on studyanalysis.analysisid = slmetadata.analysisid;

My expected result would be:

• CD1 if studydomainmetadataid is less than 0
• CD if studydomainmetadataid > 0 (i,e 85)
• 'xxx' if ANALYSISCODE is not null

I am getting null ANALYSISCODE.

Answer 1

I would guess that your query is not returning anything at all hence the CASE is not being tested and you are not getting any result returned (not even your ELSE default value).

To test this guess, wrap you query with a SELECT COUNT(*) FROM like this:

SELECT count(*) FROM 
(
select case           
       when ANALYSISCODE is null and studydomainmdata.studydomainmetadataid > 0
       then 'CD'
       when ANALYSISCODE is null and studydomainmdata.studydomainmetadataid < 0
       then 'CD1'
       when analysiscode is not null
       then ANALYSISCODE
       else 'N/A'
       end as ANALYSISCODE 
  from studyanalysis 
 inner join (slmetadata 
             inner join studydomainmdata 
                     on slmetadata.slmetadataid = studydomainmdata.slmetadataid 
                    and studydomainmdata.studydomainmetadataid=-9)
    on studyanalysis.analysisid = slmetadata.analysisid
)

If the count(*) returns 0 then my guess was correct and your query isn't returning any data at all, hence the null value.

The CASE statement can only work on records returned by the query, if there are no records retuned the CASE is not executed.

Hope it helps...