Oracle With, Case When statement missing keyword

Question

Hi I am trying to create a view in Oracle SQL Developer. I would like the view to be everything from raw_test with a new column As 'Exclusion Reason' where the exclusion reason value is 'Patient_ID_Missing' and 'Duplicate_MRN respectively.

With
Dup_MRN AS
  (SELECT *
FROM raw_test
   WHERE mrn IN (  SELECT mrn
                 FROM raw_test
             GROUP BY mrn
               HAVING COUNT (*) > 1))
Select raw_test.*,
    case when raw_test.patient_ID_CDW is null then 'Patient_ID_Missing'
    case when Dup_MRN.mrn is not null then 'Duplicate_MRN'
  End as "Exclusion_reason"
From raw_test              
Left join dup_mrn.mrn on raw_test.mrn = dup_mrn.mrn

When I run this I get the error "missing keyword" but I can't figure out what exactly I'm missing.

Thank you in advance for you help

*Brilliant! thank you all for the help, I definitely missed the second case clause in there. I thought it was something simple, you can look at a field of clover for hours before you see one with four leaves : )

Oracle With, Case When statement missing keyword

2 Answers