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
- I am aware of the obvious normalization issues implied by the query. I only wanted to demonstrate the issue.