oracle case statement in where clause

case expressions and statements in oracle 9i

The CASE expression was introduced by Oracle in version 8i. It was a SQL-only expression that provided much greater flexibility than the functionally-similar DECODE function. The PL/SQL parser didn't understand CASE in 8i, however, which was a major frustration for developers (the workaround was to use views, dynamic SQL or DECODE).

Oracle 9i Release 1 (9.0) extends CASE capabilities with the following enhancements:

  • a new simple CASE expression (8i CASE was a "searched" or "switched" expression);
  • a new CASE statement ; a PL/SQL construct equivalent to IF-THEN-ELSE; and
  • full PL/SQL support for both types of CASE expression; in SQL and in PL/SQL constructs (in 9i, the SQL and PL/SQL parsers are the same).

In this article, we will work through each of the new features and show a range of possibilities for the new syntax.

simple case expression

The simple CASE expression is new in 9i. In SQL, it is functionally equivalent to DECODE in that it tests a single value or expression for equality only. This is supposedly optimised for simple equality tests where the cost of repeating the test expression is high (although in most cases it is extremely difficult to show a performance difference over DECODE or the older searched CASE expression).

A simple CASE expression takes the following format. As with all CASE expression and statement formats in this article, it will evaluate from top to bottom and "exit" on the first TRUE condition.

The following is a contrived example of a simple CASE expression against the EMP table.

searched case expression

The searched CASE expression is the 8i variant. This is much more flexible than a simple CASE expression or DECODE function. It can conduct multiple tests involving a range of different columns, expressions and operators. Each WHEN clause can include a number of AND/OR tests. It takes the following format (note that the expressions to evaluate are included within each WHEN clause).

For example:

The following query against EMP shows how we might use searched CASE to evaluate the current pay status of each employee.

case expressions in pl/sql

As stated earlier, the SQL and PL/SQL parsers are the same from 9i onwards. This means that CASE expressions can be used in static implicit and explicit SQL cursors within PL/SQL. In addition to this, the CASE expression can also be used as an assignment mechanism, which provides an extremely elegant method for IF-THEN-ELSE-type constructs. For example, the following construct.

. can now be written as a CASE expression as follows.

This flexibility is something that DECODE doesn't provide as it is a SQL-only function. Needless to say, both simple and searched CASE expressions can be used as above. The following example shows a simple CASE expression being used to assign a variable.

We can take this example a stage further and use the CASE expression directly inside the call to DBMS_OUTPUT as follows.

Here we have removed the need for an intermediate variable. Similarly, CASE expressions can be used directly in function RETURN statements. In the following example, we will create a function that returns each employee's pay status using the CASE expression from our earlier examples.

Of course, we need to balance the good practice of rules encapsulation with our performance requirements. If the CASE expression is only used in one SQL statement in our application, then in performance terms we will benefit greatly from "in-lining" the expression directly. If the business rule is used in numerous SQL statements across the application, we might be more prepared to pay the context-switch penalty and wrap it in a function as above.

Note that in some earlier versions of 9i, we might need to wrap the CASE expression inside TRIM to be able to return it directly from a function (i.e. RETURN TRIM(CASE. )). There is a "NULL-terminator" bug similar to a quite-well known variant in 8i Native Dynamic SQL (this would sometimes appear when attempting to EXECUTE IMMEDIATE a SQL statement fetched directly from a table).

ordering data with case expressions

We have already seen that CASE expressions provide great flexibility within both SQL and PL/SQL. CASE expressions can also be used in ORDER BY clauses to dynamically order data. This is especially useful in two ways:

  • when we need to order data with no inherent order properties; and
  • when we

    need to support user-defined ordering from a front-end application.

In the following example, we will order the EMP data according to the JOB column but not alphabetically.

As stated earlier, the second possibility is for user-defined ordering. This is most common on search screens where users can specify how they want their results ordered. It is quite common for developers to code complicated dynamic SQL solutions to support such requirements. With CASE expressions, however, we can avoid such complexity, especially when the number of ordering columns is low. In the following example, we will create a dummy procedure to output EMP data according to a user's preference for ordering.

CASE expressions can only return a single datatype, so we need to cast NUMBER and DATE columns to VARCHAR2 as above. This can change their ordering behaviour, so we ensure that the format masks we use enable them to sort correctly.

Now we have the function in place, we can simulate a front-end application by setting up a refcursor variable in sqlplus and calling the function with different inputs as follows.

The overall benefits of this method are derived from having a single, static cursor compiled into our application code. With this, we do not need to resort to dynamic SQL solutions which are more difficult to maintain and debug but can also be slower to fetch due to additional soft parsing.

filtering data with case expressions

In addition to flexible ordering, CASE expressions can also be used to conditionally filter data or join datasets. In filters, CASE expressions can replace complex AND/OR filters, but this can sometimes have an impact on CBO arithmetic and resulting query plans, so care will need to be taken. We can see this as follows. First we will write a fairly complex set of predicates against an EMP-DEPT query.

We can re-write this using a CASE expression. It can be much easier as a "multi-filter" in certain scenarios, as we can work through our predicates in a much more logical fashion. We can see this below. All filters evaluating as true will be give a value of 0 and we will only return data that evaluates to 1.

As stated, care needs to be taken with this as it can change the CBO's decision paths. As we are only dealing with EMP and DEPT here, the following example ends up with the same join mechanism, but note the different filter predicates reported by DBMS_XPLAN (this is a 9i Release 2 feature). When costing the predicates, Oracle treats the entire CASE expression as a single filter, rather than each filter separately. With histograms or even the most basic column statistics, Oracle is able to cost the filters when we write them the "AND/OR way". With CASE, Oracle has no such knowledge to draw on.

case statements (pl/sql only)

We have spent a lot of time looking at CASE expressions in this article. We will finish with a look at the new CASE statement. Most developers seem to use this term when they are in fact describing CASE expressions. The CASE statement is a PL/SQL-only construct that is similar to IF-THEN-ELSE. Its simple and searched formats are as follows.

Note the semi-colons. CASE statements do not return values like CASE expressions. CASE statements are IF tests that are used to decide which action(s) or operation(s) to execute. Note also the END CASE syntax. This is mandatory. In the following example, we will return to our dummy test but call a procedure within each evaluation.

CASE statements can be useful for very simple, compact and repeated tests (such as testing a variable for a range of values). Other than this, it is unlikely to draw many developers away from IF-THEN-ELSE. The main difference between CASE and IF is that the CASE statement must evaluate to something. Oracle has provided a built-in exception for this event; CASE_NOT_FOUND. The following example shows what happens if the CASE statement cannot find a true test. We will trap the CASE_NOT_FOUND and re-raise the exception to demonstrate the error message.

The workaround to this is simple: add an "ELSE NULL" to the CASE statement.

further reading

This completes our look at CASE expressions and statements. For further reading, see the online SQL Reference and the PL/SQL User's Guide and Reference .

source code

The source code for the examples in this article can be downloaded from here .


Category: Forex

Similar articles: