469,602 Members | 1,838 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

where to use SQL Case statement ? and when ?and give example?

where to use SQL Case statement ? and when ?and give example?
Apr 22 '19 #1
2 1587
Luuk
1,044 Expert 1GB
You can use it to select even or odd numbers:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     i,
  3.     CASE WHEN i%2=1 THEN i ELSE 0 END  AS odd,
  4.     CASE WHEN i%2=0 THEN i ELSE 0 END  AS even
  5. FROM TEST
  6. WHERE i<1000
output:
Expand|Select|Wrap|Line Numbers
  1. i    odd    even
  2. 1    1    0
  3. 2    0    2
  4. 3    3    0
  5. 4    0    4
  6. 5    5    0
  7. 6    0    6
  8. 7    7    0
  9. 8    0    8
  10. 9    9    0
  11. 10    0    10
  12. 11    11    0
In simple cases (like above) there are some alternatives like IIF:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     i,
  3.     IIF (i%2=1, i, 0)  AS odd,
  4.     IIF (i%2=0, i, 0)  AS even
  5. FROM TEST
  6. WHERE i<12
but when expanding this example, you will no longer want to write this using IIF:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     i,
  3.     CASE i%4 
  4.            WHEN 3 THEN 3 
  5.            WHEN 2 THEN 2 
  6.            WHEN 1 THEN 1 
  7.            ELSE 0 END AS remainderAfterDivBy4
  8. FROM TEST
  9. WHERE i<12
output:
Expand|Select|Wrap|Line Numbers
  1. i    remainderAfterDivBy4
  2. 1    1
  3. 2    2
  4. 3    3
  5. 4    0
  6. 5    1
  7. 6    2
  8. 7    3
  9. 8    0
  10. 9    1
  11. 10    2
  12. 11    3
Apr 22 '19 #2
Rabbit
12,516 Expert Mod 8TB
Where?
Pretty much anywhere, in the select clause, or a join clause, or a where clause, or an order by clause, etc.

When?
Whenever you need to output different values based on a condition.

Examples?
See Luuk's response above.
Apr 22 '19 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Largo SQL Tools | last post: by
1 post views Thread by mirth | last post: by
5 posts views Thread by Rachel Weeden | last post: by
1 post views Thread by priyanka2203 | last post: by
1 post views Thread by Cloudbender | last post: by
13 posts views Thread by Satya | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.