CODEWITHSUNDEEP
sqloracle-databasewith-statementcase-when
MonkeySolve
MonkeySolve

Reputation: 101

Oracle With, Case When statement missing keyword

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 : )

Upvotes: 3

Views: 1233

Answers (2)

TOUZENE Mohamed Wassim
TOUZENE Mohamed Wassim

Reputation: 712

You are using CASE statement incorrectly.

SELECT raw_test.*,
    CASE
    WHEN raw_test.patient_ID_CDW IS NULL THEN 'Patient_ID_Missing'
    WHEN Dup_MRN.mrn IS NOT NULL THEN 'Duplicate_MRN'
    ELSE ''
    END AS "Exclusion_reason"

Upvotes: 1

Jacobm001
Jacobm001

Reputation: 4549

You're using the case statement incorrectly. It should have the format of 

case
  when exp1 then thing1
  when exp2 then thing2
  else default_thing
end as field_name

with up_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'
    when dup_mrn.mrn is not null 
      then 'Duplicate_MRN'
    else null
  end as "Exclusion_reason"
from 
  raw_test              
  left join dup_mrn.mrn 
    on raw_test.mrn = dup_mrn.mrn

Upvotes: 0

Related Questions