Back to Papers
 
 

Introduction

Definitions

Basic Usage

Logic-dependent Aggregation

An Interlude

Logic-dependent Aggregation (contd)

Pivot Tables and Multi-part Logic

Beyond Equality

Conclusion

Additional Resources

Please note this page has moved to http://oracledoug.com/case2.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

The first thing to note is that CASE expressions are defined in the Expressions chapter of the Oracle SQL Language Reference Manual. This offers our first hint of the power of CASE, because it indicates that we can use it wherever we might use any other expression, in the SELECT, WHERE or ORDER BY clauses for example. I like Oracle’s high level description of CASE which sums up what we’re going to use it for. “CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures.” (Note that there’s no need to use a procedural language – it’s all available in a single SQL statement.)

Here are the formal definitions of the two variants 

Simple CASE Expression

CASE expr WHEN comparison_expr_1 THEN return_expr_1 
[WHEN comparison_expr_2 THEN return_expr_2 ….] 
[ELSE default] 
END

Where :-

Expr is a valid expression that is evaluated once.

Comparison_Expr_(1-n) are compared to the Condition

Return_expr_(1-n) are the results returned if the matching Expr = Condition

default is the value returned if none of the Comparison_Exprs = Expr. If no value is specified for default and none of the Comparison_Exprs = Expr, then CASE will return NULL. 

Searched CASE Expression

CASE WHEN condition_1 THEN return_expr_1 
[WHEN condition_2 THEN return_expr_2 ….] 
[WHEN condition_n THEN return_expr_n ….] 
[ELSE default] 
END

Where :-

Condition_(1-n) are valid expressions that could be evaluated to TRUE (e.g. amount_sold > 1000; cust_last_name = ‘BURNS’; a.amount_sold / a.unit_price > b.amount_sold / b.unit_price)

Return_expr_(1-n) are the results returned if the matching condition was true.

default is the result returned if none of the WHEN conditions evaluates to TRUE. If no value is specified for default and none of the WHEN conditions are TRUE, then CASE will return NULL. 

So Oracle will evaluate each condition and as soon as one of them is TRUE, it will return the related expression that follows the THEN keyword and then exit the CASE structure. The difference between the Searched Case and Simple Case is that the latter compares a single expression against possible results, whereas the Searched Case expression allows us to test multiple conditions which may not be related.

All of which is a slightly long-winded way of describing a very simple principle. Those of you with previous programming experience in other languages may find it simpler to understand a DECODE expression as a variation on an ‘if … then … elseif …’ type of structure. (It’s the Searched Case Expression variant I’m using here)

if (condition1)
  return(result1);
elseif (condition2)
 return(result2);
…

elseif (conditionn)
 return(resultn);
else
return(default);

To finish off the definition of CASE expressions there are some important data type rules highlighted in this section of the documentation

“For a simple CASE expression, the expr and all comparison_exprs must either have the same datatype (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric datatype. If all expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

For both simple and searched CASE expressions, all of the return_exprs must either have the same datatype (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric datatype. If all return expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.”

Previous