CASE gotcha in Oracle 8i
“CASE works with all modern versions…”, that’s what Doug Burns wrote in his excellent article about CASE expressions. I believe that a clarification is needed here, especially for those of you who still deal with Oracle 8i. In Oracle 8i CASE statements and expressions are supported only in SQL and not in PL/SQL. In 8i you cannot use CASE in PL/SQL. Starting with Oracle database release 9.0.1, the SQL and PL/SQL parsers were integrated and, as a result, in version 9.0.1 and above CASE works in both SQL and PL/SQL. Here is an example:
Filed in Oracle, Tips with 16 Comments | Tags: 8i, case, expression, pl/sql, sqlCASE used in CREATE INDEX
The following is a neat way to index id1,id2 when id is not null, and index NULL,NULL (which won’t be indexed) when it is: (more…)
“Between” CASE and DECODE
Here is a select statement that demonstrates the following:
- Use of CASE to do a “between” logic.
- Use of DECODE with SIGN to do a “between” logic.
How easy and clear to use CASE as compared to using DECODE.
