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!

RELATED ARTICLESMORE FROM AUTHOR