Database

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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.

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

1 ACCEPTED SOLUTION

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

Re: SQL Case Expression

Thats fine.