Using Case expression you can achieve the IF-THEN-ELSE logic in Oracle SQL that too without invoking any kind of SQL procedures.

CASE expression can do all the work of a DECODE expression. Several books recommend using CASE over DECODE because

- CASE expressions are easier to read in comparison to DECODE expression and
- CASE expression is ANSI-compliant and forms part of the SQL/92 standard.

- Simple case expressions
- Searched case expressions

The syntax of

CASE search_expression

WHEN input_expression 1 THEN output_result 1

WHEN input_expression 2 THEN output_result 2

...

WHEN input_expression N THEN output_result N

ELSE Else_result

END

WhereWHEN input_expression 1 THEN output_result 1

WHEN input_expression 2 THEN output_result 2

...

WHEN input_expression N THEN output_result N

ELSE Else_result

END

- Search_Expression is the expression to be evaluated.
- Input_Expressions are the expressions to be evaluated against search_expression.
- Output_Results are the returned results (one for each possible expression). If expression1 evaluates to search_expression, result1 is returned, and similarly for the other expressions.
- Else_Result is returned when no matching expression is found.

Points to remember

- The data type of all the input expressions must match with the data type of search expression. Means data type of search expression and input expression must be the same.
- The datatype of all the output results must match with Else result means the data type of output result along with else result must be the same.

SELECT first_name,department_id,

(CASE department_id

WHEN 10 THEN 'Administration'

WHEN 20 THEN 'Marketing'

WHEN 30 THEN 'Purchasing'

WHEN 40 THEN 'Shipping'

WHEN 50 THEN 'Shipping'

ELSE 'Sorry'

END) Departments

FROM employees

WHERE department_id BETWEEN 10 AND 50

ORDER BY 2;

Let’s take a look at the case block of this query. We are using column department_id of employees table as our search expression and the values from department id column (10, 20, 30, 40 and 50) are serving as input expressions of WHEN-THEN pairs.(CASE department_id

WHEN 10 THEN 'Administration'

WHEN 20 THEN 'Marketing'

WHEN 30 THEN 'Purchasing'

WHEN 40 THEN 'Shipping'

WHEN 50 THEN 'Shipping'

ELSE 'Sorry'

END) Departments

FROM employees

WHERE department_id BETWEEN 10 AND 50

ORDER BY 2;

Whenever we specify a column name of a table as search expression the oracle engine treats all the data from this column as an array and matches all the input_expressions with every element of this array and if a match is found, it returns the corresponding result values, Otherwise the else statement gets return.

SELECT

CASE 'Dog'

WHEN 'Cat' THEN '1 Flase'

WHEN 'Dog' THEN '2 True'

WHEN 'Cow' THEN '3 False'

WHEN 'Dog' THEN '4 True'

ELSE 'Sorry'

END

FROM dual;

Here in this simple case, the search expression is ‘Dog’ along with 4 When-and-Then pairs. The input expression of 2nd and 4th pair is a string “Dog” which is a perfect match for our search expression but on executing this query, oracle engine will return the result value from the first best match which in our case is the 2nd pair.CASE 'Dog'

WHEN 'Cat' THEN '1 Flase'

WHEN 'Dog' THEN '2 True'

WHEN 'Cow' THEN '3 False'

WHEN 'Dog' THEN '4 True'

ELSE 'Sorry'

END

FROM dual;

So that is it guys on Case Expression. Hope it helped you. Kindly please share it on your social network so that I can reach more people. Thanks & have a great day!

You can **DOWNLOAD** SQL script and presentation used in the Video and in this article.

I have used copy cloud to share these resourses. You can also join copy cloud and get 15GB free cloud storage for lifetime. If you will use this referal link https://copy.com?r=j7eYO7 You will get 5GB extra free cloud storage means total 20GB free cloud storage.