CODEWITHSUNDEEP
sql
ashur
ashur

Reputation: 63

how could I use sql 'case when' correctly in Athena

select  
 (case "plm"."event" when ('newMessage' and plm.id == vm.id) then 'VoiceMessgae' else plm."event" end) as "event"
   FROM 
   others_messages plm
   left join voice_messages vm on plm.id = vm.id

it shows no viable alternative at input '(case "plm"."event" when ('newMessage' and plm.id =='

how could I fix it?

Upvotes: 5

Views: 20772

Answers (2)

GMB
GMB

Reputation: 222622

I think you want:

select
    case when "plm"."event" = 'newMessage' and vm.id is not nulll
        then 'VoiceMessgae' 
        else plm."event" 
    end as "event" 
from others_messages plm 
left join voice_messages vm on plm.id = vm.id

You don't need to perfom the equality check in the case condition - the left join does that already. Just ensure that the id from the left joined table is not null (in other words: there was a match in the left join).

If find that the logic might be more clearly expressed with exists than with a left join:

select
    case when "plm"."event" = 'newMessage' and exists (select 1 from voice_messages vm where plm.id = vm.id)
        then 'VoiceMessgae' 
        else plm."event" 
    end as "event" 
from others_messages plm

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

To be syntactically correct, the case expression would be:

select (case when "plm"."event" = 'newMessage' and plm.id = vm.id
             then 'VoiceMessgae'
             else plm."event"
        end) as "event"

case offers two syntaxes. If you have complex expressions, you need to use the searched case where the boolean expression follows the when.

Upvotes: 6

Related Questions