Nested CASE statements 1

[bustercoder]

Hello, I have the following sql statement in my stored proc to handle a divide by zero error:

[DISCOUNT %] = CASE SUM(SOP10100.SUBTOTAL) WHEN 0 THEN 0 ELSE CAST(ROUND(SUM(SOP10100.TRDISAMT) / SUM(SOP10100.SUBTOTAL) * 100.0,2) AS NUMERIC (19, 2 )) END,

But now I also need to use in this statement for another result, which already uses a CASE statement:

[DISC WARNING] = CASE WHEN CAST(ROUND(SUM(SOP10100.TRDISAMT) / SUM(SOP10100.SUBTOTAL) * 100.0,2) AS NUMERIC (19, 2)) <> 50.00 THEN CAST(ROUND(SUM(SOP10100.TRDISAMT) / SUM(SOP10100.SUBTOTAL) * 100.0,2) AS NUMERIC (19, 2)) ELSE null END

Can someone please show me how I could do this or tell me if I need another approach? I've tried several different statements (nested statements) but I'm having some trouble getting the syntax right.

Thanks,
Buster

 

[monksnake]

Since I really don't understand what all that is, I don't know another way to do it, but I came up with this:

CASE SUM(SOP10100.SUBTOTAL) WHEN 0 THEN null ELSE 
  CASE WHEN CAST(ROUND(SUM(SOP10100.TRDISAMT) / SUM(SOP10100.SUBTOTAL) * 100.0,2) AS NUMERIC (19, 2)) <> 50.00 THEN CAST(ROUND(SUM(SOP10100.TRDISAMT) / SUM(SOP10100.SUBTOTAL) * 100.0,2) AS NUMERIC (19, 2)) ELSE null END END

If by any chance SUM(SOP10100.SUBTOTAL) = 0 then I have that entire large case return null otherwise it does all that the second case that you had listed does.

<.