Nested Case Statements

[dba112233]

I can't get the syntax right on my nested CASE statements nor have I found anything on the web pertaining to nested SQL CASE statements:

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance

FROM RMASTER rm


ERRORS:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'

 

[gmmastros]

Each CASE must have a corresponding END. I count 9 CASE's and only 3 END's.

I'm just guessing here, but it this what you want.

SELECT  rm.rmsacctnum AS [Rms Acct Num], 
        SUM(rf.rmstranamt) AS [TranSum], 
        SUM(rf10.rmstranamt10) AS [10Sum],
        CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
               CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
                    SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
                    WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
                    SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
                    WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
                    SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
                    WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
                    SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
               END
          ELSE
               CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
                    SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)     
                    WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
                    SUM(rf10.rmstranamt10) + (rf.rmstranamt)     
                    WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
                    SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)     
                    WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
                    SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
               END     
          END AS [Balance],
        cb.CurrentBalance

FROM RMASTER rm

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dba112233]

thanks, I had to take out the CASE as you showed.