CODEWITHSUNDEEP
sqloracle-databasejoin
Zeus
Zeus

Reputation: 6566

Oracle sql join with case statement

I have two tables, and the join condition will have to depend on a certain value of the 2nd table. In the below sql the line is the join condition I'm trying to implement, but it throws compilation errors, not sure how to do this.

Note: This is a part of a big query, join will be efficient way to do.

Line throwing compilation error

data.hasIndicator in (case when prim.ind = 'YES' then 'YES' when prim.ind = 'NO' then ('YES','NO') end)

-- Full sql

with data as
  (select
     'YES' hasIndicator, 'Gold' item, 12 itemId
   from dual
   union
   select
     'YES' hasIndicator, 'Silver' item, 13 itemId
   from dual
   union
   select
     'NO' hasIndicator, 'Platinum' item, 14 itemId
   from dual),
prim as
  (select
     'NO' ind, 1 id
   from dual
   union
   select
     'YES' ind, 2 id
   from dual)
select *
from prim
  join data on
    data.hasIndicator in (case when prim.ind = 'YES' then ('YES') when prim.ind = 'NO' then ('YES','NO') end)
where
  id = 1;

Upvotes: 0

Views: 2957

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

case doesn't return a list. It returns a single value. You can write the logic as:

select *
from prim join
     data 
     on (prim.ind = 'YES' and data.hasIndicator = 'YES') or
        (prim.ind = 'NO' and data.hasIndicator IN ('YES', 'NO'))
where id = 1;

or, you could slightly simplify this to

     on (prim.ind = data.hasIndicator) or (data.hasIndicator = 'YES')

Upvotes: 2

Related Questions