Oracle – Using Same CASE WHEN Conditions for Multiple Query Columns

best practicesdatabase-designoracleoracle-10g

Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once?

See the example below.

SELECT
    CASE testStatus 
        WHEN 'A' THEN 'Authorized'
        WHEN 'C' THEN 'Completed'
        WHEN 'P' THEN 'In Progress'
        WHEN 'X' THEN 'Cancelled'
    END AS Status,

    CASE testStatus 
        WHEN 'A' THEN authTime
        WHEN 'C' THEN cmplTime
        WHEN 'P' THEN strtTime
        WHEN 'X' THEN cancTime
    END AS lastEventTime,

    CASE testStatus 
        WHEN 'A' THEN authBy
        WHEN 'C' THEN cmplBy
        WHEN 'P' THEN strtBy
        WHEN 'X' THEN cancBy
    END AS lastEventUser
FROM test

In non-sql psuedo-code, the code might look like:

CASE testStatus
    WHEN 'A'
        StatusCol        = 'Authorized'
        lastEventTimeCol = authTime 
        lastEventUserCol = authUser
    WHEN 'C'
        StatusCol        = 'Completed'
        lastEventTimeCol = cmplTime
        lastEventUserCol = cmplUser
    ...
END

Note:

  • I am aware of the obvious normalization issues implied by the query. I only wanted to demonstrate the issue.

Best Answer

Even in Oracle (and in fact in the SQL standard), CASE is an expression that returns a single value. It is not used for control of flow like it is in some other languages. Therefore, it can't be used to conditionally decide among multiple columns or other operations.

I'd say put the longer version of the code (that already works) in a view, and don't worry about it in your formal queries.

You might also consider a more normalized design. For example why not store he auditing details in a separate table, with type as part of the key? This makes your code much easier to maintain, especially as more types are added...

Related Question