Simple Case Expression.
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.
In Oracle there are two types of CASE expressions
- Simple case expressions
- Searched case expressions
Simple Case Expression
As the name suggests it is the simplest version of CASE expression in other words we can say that it is a flexible and easiest version of DECODE expression.
The syntax of “Simple Case Expression” is
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
Where
- 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.
The Simple Case Expression uses search expression to determine the return value. Means simple case expression evaluates all the input expressions against the search expression. Once a match is found the corresponding result is returned otherwise Else result is displayed. Furthermore the Else statement is optional so in case you omit it then Null is displayed when the simple case expression cannot find a match.
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.
The maximum number of arguments in a CASE expression is 255. All expressions count toward this limit, including the initial expression of a simple CASE expression and the optional ELSE expression. Each WHEN … THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the output_result itself is a CASE expression.
Query 1: Column Name as Search Expression.
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.
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.
Query 2: String as Search Expression.
In case you specify a string instead of a column name as a search expression then oracle will find the first best fit match and then return the corresponding result value.
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.
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!