0 Replies Latest reply: Aug 1, 2012 1:10 AM by 953158 RSS

    Case Statement with inner Join

    953158
      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