Oracle CASE Expression


/ Published in: SQL
Save to your folder(s)

A few examples of the Oracle CASE expression


Copy this code and paste it in your HTML
  1. DROP TABLE customers;
  2.  
  3. CREATE TABLE CUSTOMERS (
  4. first_name varchar2(100),
  5. last_name varchar2(100),
  6. country varchar2(20),
  7. employees NUMBER
  8. );
  9.  
  10. INSERT INTO CUSTOMERS (first_name, last_name, country, employees) VALUES ('John', 'Smith', 'USA', 4);
  11. INSERT INTO CUSTOMERS (first_name, last_name, country, employees) VALUES ('Sally', 'Jones', 'USA', 10);
  12. INSERT INTO CUSTOMERS (first_name, last_name, country, employees) VALUES ('Steve', 'Brown', 'Canada', 15);
  13. INSERT INTO CUSTOMERS (first_name, last_name, country, employees) VALUES ('Mark', 'Allan', 'UK', 23);
  14. INSERT INTO CUSTOMERS (first_name, last_name, country, employees) VALUES ('Adam', 'Cooper', 'USA', 55);
  15.  
  16.  
  17.  
  18. SELECT * FROM CUSTOMERS;
  19.  
  20. /* Examples */
  21.  
  22. /* 1 - Simple with text */
  23.  
  24. SELECT first_name, last_name, country,
  25. CASE country
  26. WHEN 'USA' THEN 'North America'
  27. WHEN 'Canada' THEN 'North America'
  28. WHEN 'UK' THEN 'Europe'
  29. WHEN 'France' THEN 'Europe'
  30. ELSE 'Unknown'
  31. END
  32. FROM customers
  33. ORDER BY first_name, last_name;
  34.  
  35. /* 2 - same as 1 but with col name */
  36. SELECT first_name, last_name, country,
  37. CASE country
  38. WHEN 'USA' THEN 'North America'
  39. WHEN 'Canada' THEN 'North America'
  40. WHEN 'UK' THEN 'Europe'
  41. WHEN 'France' THEN 'Europe'
  42. ELSE 'Unknown'
  43. END Continent
  44. FROM customers
  45. ORDER BY first_name, last_name;
  46.  
  47. /* 3 - searched case */
  48.  
  49. SELECT first_name, last_name, country,
  50. CASE
  51. WHEN country = 'USA' THEN 'North America'
  52. WHEN country = 'Canada' THEN 'North America'
  53. WHEN country = 'UK' THEN 'Europe'
  54. WHEN country = 'France' THEN 'Europe'
  55. ELSE 'Unknown'
  56. END Continent
  57. FROM customers
  58. ORDER BY first_name, last_name;
  59.  
  60. /* 4 - searched with numbers */
  61.  
  62. SELECT first_name, last_name, employees,
  63. CASE
  64. WHEN employees < 10 THEN 'Small'
  65. WHEN employees >= 10 AND employees <= 50 THEN 'Medium'
  66. WHEN employees >= 50 THEN 'Large'
  67. END SizeOfCompany
  68. FROM customers
  69. ORDER BY first_name, last_name;
  70.  
  71. /* 5 - IN for multiple conditions */
  72.  
  73. SELECT first_name, last_name, country,
  74. CASE
  75. WHEN country IN ('USA', 'Canada') THEN 'North America'
  76. WHEN country IN ('UK', 'France') THEN 'Europe'
  77. ELSE 'Unknown'
  78. END Continent
  79. FROM customers
  80. ORDER BY first_name, last_name;
  81.  
  82. /* 6 - case within case */
  83.  
  84. SELECT first_name, last_name, country,
  85. CASE
  86. WHEN country IN ('USA', 'Canada') THEN
  87. (CASE WHEN first_name = 'Sally' THEN 'North America F' ELSE 'North America M' END)
  88. WHEN country IN ('UK', 'France') THEN
  89. (CASE WHEN first_name = 'Sally' THEN 'Europe F' ELSE 'Europe M' END)
  90. ELSE 'Unknown'
  91. END Continent
  92. FROM customers
  93. ORDER BY first_name, last_name;
  94.  
  95.  
  96. /* 7 - functions */
  97.  
  98. SELECT first_name, last_name, employees,
  99. CASE
  100. WHEN MOD(employees, 2) = 0 THEN 'Even Number of Employees'
  101. WHEN MOD(employees, 2) = 1 THEN 'Odd Number of Employees'
  102. ELSE 'Unknown'
  103. END OddOrEven
  104. FROM customers
  105. ORDER BY first_name, last_name;
  106.  
  107. /* 8 Multiple matches */
  108.  
  109. SELECT first_name, last_name, employees,
  110. CASE
  111. WHEN employees < 1 THEN 'No Employees'
  112. WHEN employees < 10 THEN 'Small'
  113. WHEN employees <= 50 THEN 'Medium'
  114. WHEN employees >= 50 THEN 'Large'
  115. END SizeOfCompany
  116. FROM customers
  117. ORDER BY first_name, last_name;
  118.  
  119.  
  120. /* 9 CASE in a WHERE clause */
  121.  
  122.  
  123. SELECT first_name, last_name, country
  124. FROM customers
  125. WHERE
  126. (CASE
  127. WHEN country IN ('USA', 'Canada') THEN 'North America'
  128. WHEN country IN ('UK', 'France') THEN 'Europe'
  129. ELSE 'Unknown'
  130. END) = 'North America'
  131. ORDER BY first_name, last_name;

URL: http://www.databasestar.com/oracle-case-statement/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.