By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,826 Members | 2,384 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,826 IT Pros & Developers. It's quick & easy.

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

P: 22
where to use SQL Case statement ? and when ?and give example?
Apr 22 '19 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,035
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
Expert Mod 10K+
P: 12,366
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.