SQL Case Expression

Database
Junior Supporter

SQL Case Expression

I know I can write

case var1
    when 'A' then 10
    when 'B' then 20
    when 'C' then 30
    when 'D' then 10
    when 'E' then 20
    else 100
end

As you can see 'A' and 'D' convert to the same value of 10 and 'B' and 'E' convert to 20.

 

Is there a valid shorter version of this Case expression? I am thinking something like the below that is a valid SQL

case var1
    when 'A', "D' then 10
    when 'B', 'E' then 20
    when 'C' then 30
    else 100
end

Any suggestions are welcomed.

 


Accepted Solutions
Teradata Employee

Re: SQL Case Expression

Then then answer is just no.

 

1 ACCEPTED SOLUTION
4 REPLIES 4
Teradata Employee

Re: SQL Case Expression

Hi sjuolay,

 

SQL case has two constructions.

The first one, similar to Oracle's decode function, which is a shortcut for testing equalities.

 

The second construct allows you more complex expressions.

From your example, you can write :

case
  when var1 in ('A', 'D') then  10
  when var1 in ('B', 'E') then  20
  when var1  =  'C'       then  30
                          else 100
end

Also keep somewhere in mind the when expressions are evaluated top to bottom, exiting the case on the first matching expression.

Junior Supporter

Re: SQL Case Expression

Hi Walder,

 

Thank you for your email. I should have been more explicit. I was hoping for a short cut in the first construction.

Teradata Employee

Re: SQL Case Expression

Then then answer is just no.

 

Junior Supporter

Re: SQL Case Expression

Thats fine.