All legitimate Oracle experts publish their Oracle qualifications. Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page.
You can use a CASE expression in any statement or clause that accepts a valid expression. Both formats support an optional ELSE clause. The simple CASE expression matches an expression to a list of simple expressions to determine the result.
In this syntax, Oracle compares the input expression e to each comparison expression e1, e2, …, en. If the input expression equals any comparison expression, the CASE expression returns the corresponding result expression r. Avoid mixing types in the then clauses.
If needed, use cast to explicitly convert them to the required type. Pivot — Rows to Columns — case in aggregate functions. More to follow: Subscribe to the newsletter! The case expression was introduced with intermediate SQL Nowadays virtually all SQL databases support this feature.
The SQL standard does not define the procedure how to resolve case expressions. In particular, the standard does not mandate to check the conditions in the order of their appearance in the case expression.
As long as the implementation yields the right result eventually, everything is allowed. Even though different evaluation procedures must produce the same result, different procedures may cause different side effects.
When using data-changing functions, for example, they might be called even if it is not strictly necessary—nevertheless you might see their side effects: the changed data. Likewise, whether or not a runtime error occurs might depend on the evaluation procedure: if the error is in a part of the case expression is that is not necessarily evaluated, it may or may not be triggered during evaluation. Finally, the time the evaluation takes is also a side effect that depends on the evaluation process.
Considering the last side effect—the performance—the obvious evaluation procedure is to check the when clauses in order until the first true condition is found. Of course it makes also sense to evaluate only one result—either the corresponding then clause or the else clause. Some products even document this evaluation procedure. Nevertheless there are cases that can lead to surprising side effects.
One common example that can cause surprising side effects is this: some products evaluate constant expressions early—i. This is very similar to a compiler that resolves constant expressions e. If this optimization affects a part of a case expression, the evaluation order gets disturbed. In SQL Server, the return type of the isnull function is always the type of the first argument. If the second argument has, for example, greater precision, significant data might get lost.
The decode function demonstrates that proprietary functions might do completely different things in different products. In MySQL, decode is a deprecated cryptographic function. In the Oracle Database it is a proprietary short form of case. The function if is a proprietary short form for a searched case expression with a single when clause—very similar to the ternary operator? He is on a mission to introduce developers to the evolution of SQL in the 21st century.
0コメント