This discussion is archived
0 Replies Latest reply: Jul 31, 2012 11:10 PM by 953158 RSS

Case Statement with inner Join

953158 Newbie
Currently Being Moderated
I didn't have any error on the belowcode. But i didnt met my My excepted result . Any one pls help me to get the excepted result. Excepted result is pasted below. And sample table and relevant values also i pasted FYR.

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


STUDYDOMAINMDATA TABLE DATA(STUDYDOMAINMETADATAID wont accept negative values)(But for testing purpose passingnegative values)

STUDYDOMAINMETADATAID -- SLMETADATAID

11 -- 25

12 -- 25

13 -- 28

SLMETADATA

SLMETADATAID ANALYSISID

25 -- 12

26 -- 26

27 -- 13

28 -- --(NULLABLE COLUMN)

STUDYANALYSIS

ANALYSISID ANALYSISCODE

12 -- XXX

26 -- YYY

28 -- ZZZ

IF I PASS SDM.STUDYDOMAINMETADATAID = 11 THEN EXCEPTED RESULT WOULD BE ‘XXX’

SDM.STUDYDOMAINMETADATAID = -1 THEN EXCEPTED RESULT WOULD BE ‘CD1’

SDM.STUDYDOMAINMETADATAID = 13 THEN EXCEPTED RESULT WOULD BE ‘CD’ BECAUSE THERE IS NO ANALYSISID FOR THE SLMETADATID 28.

Edited by: 950155 on Aug 1, 2012 10:01 AM

Edited by: 950155 on Aug 1, 2012 11:39 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points