Nested Case Statements

[jenni]

I have the following ORDER BY statement in a mySQL query:
(This is mySQL not PHP, but the PHP tags preserved the formatting.)

ORDER BY
-- List open transactions first
transStatus.consideredOpen desc,
CASE trans.transSLAstatus
	-- The service level agreement status is a warning
	WHEN 1 THEN 1
	-- We missed our service level agreement
	WHEN 2 THEN 2
	-- The service level agreement is AOK
	ELSE 3
END,
-- The customer is a VIP
cust.vip desc,
-- It is a priority transaction
trans.transactionPriority desc

I would like to change it slightly so that if a transaction is open, it goes through the other order by stuff. But, if it is closed, it will order it by something different. The ORDER BY statement below does not work, but it kind-of represents what I am trying to do.

ORDER BY
CASE transStatus.consideredOpen
	-- The transaction is open
	WHEN 1 THEN
		CASE trans.transSLAstatus
			-- The service level agreement status is a warning
			WHEN 1 THEN 1
			-- We missed our service level agreement
			WHEN 2 THEN 2
			-- The service level agreement is AOK
			ELSE 3
		END,
		-- The customer is a VIP
		cust.vip desc,
		-- It is a priority transaction
		trans.transactionPriority desc
		-- The transaction is closed
	ELSE
		-- Order it by the time the transaction was submitted
		trans.time_opened
END

I am sure it is possible. I just can't seem to get my head around it. Help would be very much appreciated.

[tomhath]

Your original ORDER BY has all open transactions listed first, followed by the other transactions. I assume you want to keep that.

So for starters, the first level of ordering is Open/Closed.

Now what would be the second level or ordering (within an open or closed transaction)? It has to be the same data type for every row, whether the transaction is open or closed. So logically you can't mix integers (SLAStatus) and times (time_opened), but you can get away with it by thinking of the time as an integer timestamp. Alternatively you could use hardcoded times for the SLAStatus order values (it doesn't matter what times you use, they're only used for sorting), e.g:
SLAStatus=1 use '01/01/2000 01:00',
SLAStatus=2 use '01/01/2000 02:00',
SLAStatus=3 use '01/01/2000 03:00'

The third and fourth levels of ordering (VIP and Priority) only matter for open transactions so you can default them to some constant for closed transactions.

ORDER BY
-- First level: Open/Closed transactions
transStatus.consideredOpen desc,

-- Second level: SLAStatus or time_opened
CASE transStatus.consideredOpen
    WHEN 1 THEN  

        CASE trans.transSLAstatus
            -- The service level agreement status is a warning
            WHEN 1 THEN '01/01/2000 01:00'
            -- We missed our service level agreement
            WHEN 2 THEN '01/01/2000 02:00'
            -- The service level agreement is AOK
            ELSE '01/01/2000 03:00'
        END
    ELSE
        -- Order it by the time the transaction was submitted
        trans.time_opened, 
    END,

-- Third level: VIP or not used
CASE transStatus.consideredOpen
    WHEN 1 THEN
       -- The customer is a VIP
       cust.vip
    ELSE
      -- closed, default to 0

END  desc,

-- Fourth level: Priority transaction or not used
CASE transStatus.consideredOpen
    WHEN 1 THEN
       trans.transactionPriority
    ELSE
      -- closed, default to 0

END  
END desc

[Sxooter]

Have you tried that syntax? Did it work? <--- this is directed to the OP, jenni

If not try using the other case syntax:

case
when a=1 and b=2 then 'abc'
when a=1 and b=1 then 'xyz'
when a=2 and b=1 then 'def'
when a=2 and b=2 then 'jkl'
end

[jenni]

Thank you both so much for your responses. I ended up taking pieces from both of your responses to get it working. I did run into a nasty issue with NULLs, but with an obscene amount trial and error after some hefty googling, I think all is good now.

Thank you again!

[Sxooter]

Congrats! Can we see what your final query looked like?!

don't forget to mark the thread resolved.