Nested CASE statements Problem

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'

I think there are two problems:

(a) You have a THEN {something} & {a new CASE statement} ==> this needs to be seperated with an ELSE

(b) Each CASE WHEN needs an END

I was not able to completely modify your T-SQL...but your changes should look similar to:

select case when 1=1 then
case when 2=2 then 1 else
case when 3=3 then 2 else 1
end
end
end

Peter

|||I think I probably should just use IF statements inside my first CASE statement|||

Well, this throws an error also.

The error is:

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'IF'.

Msg 147, Level 15, State 1, Line 5

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Msg 147, Level 15, State 1, Line 9

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

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

END

ELSE

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf10.rmstranamt10) + (rf.rmstranamt)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)

END

END

END AS [Balance],

cb.CurrentBalance

FROM RMASTER rm

|||

yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .

case 1 and Case 4 of your outer else are the same. . .

should it have read:

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

now if your first test in the outer case:

if ArgA > ArgB then this is never true:

ArgA < 0 AND ArgB > 0

then wouldn't all of the first half of your outer case logic translate to

if ArgA>ArgB then ArgA - Abs(ArgB)

now the else part:

if ArgB < ArgA then this is never true:

ArgA > 0 AND ArgB < 0

then wouldn't all of the else half of your outer case logic translate to

ArgB - abs(ArgA)

so simply put, wont this do it -

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

|||

I ended up having to take out each CASE after my first CASE in my nested CASE statement which if you think about it is the correct syntax

|||

typo on my last statement (change is bolditalic)

yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .

case 1 and Case 4 of your outer else are the same. . .

should it have read:

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

now if your first test in the outer case:

if ArgA > ArgB then this is never true:

ArgA < 0 AND ArgB > 0

then wouldn't all of the first half of your outer case logic translate to

if ArgA>ArgB then ArgA - Abs(ArgB)

now the else part:

if ArgB > ArgA then this is never true:

ArgA > 0 AND ArgB < 0

then wouldn't all of the else half of your outer case logic translate to

ArgB - abs(ArgA)

so simply put, wont this do it -

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

|||

sth is illogic in your code man, some cases are just useless, you can delete them, because there is no way that they occur.

otherwise, just take off the case words from inside (to have a correct syntax)