CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Dikshit Karki
Dikshit Karki

Reputation: 131

Case when statement while using in join condition not working in oracle

I need to use case when on join statement in sql.I tried, but I am getting error as:

Missing expression:ORA-00905

I tried this query:

SELECT * 
FROM   abc a 
       LEFT JOIN (SELECT * 
                  FROM   anil_groups 
                  WHERE  datatype = 'C' 
                         AND payor = 'SELECT') gr 
              ON CASE 
                   WHEN Upper(a.sourcefilename) LIKE '%RIO%' THEN 
                   gr.sourceid = 'SH_Rio_Tinto' 
                   ELSE gr.sourceid = 'SH_Albertson' 
                 END 
       LEFT JOIN (SELECT * 
                  FROM   tbl_danger 
                  WHERE  source = 'KK') spec 
              ON Upper(a.rollupid) = spec.code;

Upvotes: 0

Views: 77

Answers (1)

Caius Jard
Caius Jard

Reputation: 74690

It doesnt work like that in oracle. CASE WHEN is a construct that provides a non boolean value, so you have to use it to run your boolean test (Upper(a.sourcefilename) LIKE '%RIO%') and have it produce the string value you want to compare to gr.sourceid:

ON gr.sourceid = CASE 
                   WHEN Upper(a.sourcefilename) LIKE '%RIO%' THEN 
                    'SH_Rio_Tinto' 
                   ELSE 'SH_Albertson' 
                 END

You can also rewrite your join not to use it:

ON (gr.sourceid = 'SH_Rio_Tinto' AND Upper(a.sourcefilename) LIKE '%RIO%') OR
   (gr.sourceid = 'SH_Albertson' AND Upper(a.sourcefilename) NOT LIKE '%RIO%')

Upvotes: 1

Related Questions