August 7, 2018 at 3:26 am
I've been trying for the past hour to nest a case in another case.
How can I say this exactly?
CASE WHEN CONFIG.PremiumSignShowData>0
THEN
CASE SIGN (ISNULL(resultPremium,0)) WHEN '-1' THEN '-' ELSE '+'
ELSE ' '
END AS PremiumSign
Thanks in advance
August 7, 2018 at 3:30 am
You need another END for the inner case:
SELECT
CASE WHEN CONFIG.PremiumSignShowData>0
THEN CASE SIGN (ISNULL(resultPremium,0))
WHEN '-1' THEN '-'
ELSE '+'
END
ELSE ' '
END AS PremiumSign
Adi
August 7, 2018 at 3:31 am
Hi,
You are missing the END in line no 3 at the end. It should be like:
SELECT CASE WHEN CONFIG.PremiumSignShowData>0
THEN
CASE SIGN (ISNULL(resultPremium,0)) WHEN '-1' THEN '-' ELSE '+' END
ELSE ' '
END AS PremiumSign
August 7, 2018 at 3:39 am
Done thanks guys.
August 7, 2018 at 6:18 am
Just an FYI, but that code introduces an unnecessary implicit conversion by using a string to represent negative 1. Here's the code fully indented and just removing the single quotes around -1:SELECT
CASE
WHEN CONFIG.PremiumSignShowData > 0 THEN
CASE SIGN (ISNULL(resultPremium, 0))
WHEN -1 THEN '-'
ELSE '+'
END
ELSE ' '
END AS PremiumSign
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 7, 2018 at 7:34 am
You can actually simplify this. There is no reason for the nested CASE expressions and you can greatly simplify the sign.
I'm assuming that CONFIG.PremiumSignShowData is both a BIT field and not nullable. If that's not the case, you should have a very good reason why. The rewrite will still work, but it will be a bit more complicated.
The conditions for showing ' ' are the least complicated, so test that first. Also, there is no reason to handle NULL values if they are just going to fall into the ELSE condition, because they are going to fall into the ELSE condition anyhow.
SELECT
CASE
WHEN CONFIG.PremiumSignShowData = 0 THEN ' '
WHEN resultPremium < 0 THEN '-'
ELSE '+'
END AS PremiumSign
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 8, 2018 at 8:01 am
and the moral of this story is to indent your code properly 🙂 If you had done that you would have quickly spotted the missing END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply