asked on
Nested Case statement
This case statement id failing.. What could be the issue
-1.0*SUM(CASE WHEN L1_IS="X1" AND CAST(ISCON AS INTEGER)=1 AND LTID=1 AND CTID = 2 AND (FA="PV" OR FA="TP " OR FA="FV")
THEN
CASE WHEN @Year! = 'Y1' THEN COALESCE(A1*1.0,0.0)
WHEN @Year! = 'Y2' THEN COALESCE(A2*2.0,0.0)
WHEN @Year! = 'Y3' THEN COALESCE(A3*3.0,0.0) ELSE 0 END
ELSE 0.0 END) AS Column
Microsoft SQL Server
Last Comment
Vitor Montalvão
Failing because it is not giving the expected result or because an error is occurring? If the latter, provide the error message.
BTW, are those quotation marks (") in line 1? SQL generally uses apostrophes (').
Not sure if this is the problem, but I would recommend returning the same data type from the inner case statement. In other words, change this:
to this:
WHEN @Year! = 'Y3' THEN COALESCE(A3*3.0,0.0) ELSE 0 END
to this:
WHEN @Year! = 'Y3' THEN COALESCE(A3*3.0,0.0) ELSE 0.0 END
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.