CODEWITHSUNDEEP
sqlamazon-redshift
Harshit Bhatt
Harshit Bhatt

Reputation: 3

Mutliple values for IN clause with CASE in Redshift

I am trying to run this query where IN clause uses CASE to choose values between two cases.

The issue is with the hard coded value('aaa','bbb'). I cannot add multiple values inside THEN so it act as regular IN values. The hard code values will be dynamic as I will pass a variable for it.

select kdo.field0
from tb1 data1 inner join tb2 kdo
on kdo.field1 = 'xxx'
   and kdo.field2::DATE >='2017-08-01'::DATE
   and kdo.field0
       in (case when 'asd'!='' then 'aaa','bbb'
           else tb2.field0 end);

Also, I used a sub-query select inside THEN to get specific hard code values but it is also of no avail. Using single hard-coded value obviously works as usual.

Upvotes: 0

Views: 1822

Answers (1)

AlexYes
AlexYes

Reputation: 4208

Move your CASE outside IN:

select kdo.field0
from tb1 data1 inner join tb2 kdo
on kdo.field1 = 'xxx'
   and kdo.field2::DATE >='2017-08-01'::DATE
   and case when 'asd'!='' then kdo.field0 in ('aaa','bbb')
       else kdo.field0=tb2.field0 end;

however I'm not sure what do you mean by 'asd'!='' since 'asd' is a string and this will always return true

also, else tb2.field0 end); part in your statement is not an array option, it's a column name so I assume this just translates to kdo.field0=tb2.field0 because if the previous case option is false you want to check if kdo.field0 is equal to any of values in tb2.field0 which is basically a join condition

Upvotes: 1

Related Questions