Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question
    
And all these columns authTime, authUser, cmplTime are in the same table? –  ypercube May 21 '12 at 21:40

2 Answers 2

up vote 4 down vote accepted

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...

share|improve this answer

If all these columns are from the same table, you can use something like this:

    SELECT  
        'Authorized' AS StatusCol,
        authTime     AS lastEventTimeCol, 
        authUser     AS lastEventUserCol 
    FROM test
    WHERE testStatus = 'A'

  UNION ALL

    SELECT  
        'Completed', 
        cmplTime,
        cmplUser    
    FROM test
    WHERE testStatus = 'C'

  UNION ALL

    SELECT  
        'In Progress', 
        strtTime,
        strtUser    
    FROM test
    WHERE testStatus = 'P'

  UNION ALL

    SELECT  
        'Cancelled', 
        cancTime,
        cancUser    
    FROM test
    WHERE testStatus = 'X' ;
share|improve this answer
2  
I pondered posting that, but it's just as long-winded. I don't think there's a nice way of doing it. –  Phil May 21 '12 at 21:53
    
It answers the question +1, but as others have indicated, what you started with is better. You could also concatenate the values that correspond to each testStatus and then split them apart, but that would be even worse. –  Leigh Riffel May 22 '12 at 12:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.