January 29, 2016 at 12:46 pm
I am trying to do a case statement without having to a bunch of and's and or's. This is being done in a set statement.
I tried to do this:
RetailBonusAmount = CASE WHEN ISNULL(oa.IsRetail,odr. IsRetail) = 1 THEN
CASE WHEN DealerID = 5 THEN 5 ELSE 10 END
this gives me an error. It says "Incorrect syntax near from.
This actually goes down a couple of levels of nested case statements. But the issue seems to be no way to do a:
Case when something happens THEN WHEN something else happens...
The THEN seems to require a value.
I had thought you could nest CASE statements.
Thanks,
Tom
January 29, 2016 at 12:49 pm
You can, you're missing an END.
CASE WHEN ISNULL(oa.IsRetail,odr. IsRetail) = 1 THEN
CASE WHEN DealerID = 5 THEN 5
ELSE 10
END
ELSE <whatever it should return if the ISNULL evaluates to anything other than 1>
END
Each of the CASEs is a separate expression of the form CASE WHEN <condition> THEN <expression> ... ELSE <expression> END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2016 at 12:50 pm
tshad (1/29/2016)
I am trying to do a case statement without having to a bunch of and's and or's. This is being done in a set statement.I tried to do this:
RetailBonusAmount = CASE WHEN ISNULL(oa.IsRetail,odr. IsRetail) = 1 THEN
CASE WHEN DealerID = 5 THEN 5 ELSE 10 END
this gives me an error. It says "Incorrect syntax near from.
This actually goes down a couple of levels of nested case statements. But the issue seems to be no way to do a:
Case when something happens THEN WHEN something else happens...
The THEN seems to require a value.
I had thought you could nest CASE statements.
Thanks,
Tom
You absolutely can nest them. You were missing an END. It helps to over-tabify these to see where they start and end
RetailBonusAmount = CASE
WHEN ISNULL(oa.IsRetail,odr. IsRetail) = 1 THEN CASE
WHEN DealerID = 5 THEN 5
ELSE 10
END
END -- was missing
One nitpick, there is no such thing as a CAST statement. It is a CASE expression.
EDIT: fix formatting in code (spaces for tabs)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 29, 2016 at 12:50 pm
You can nest CASE statements. You only have an END for one of them, though.
Cheers!
EDIT: Heh, looks like this got pounced on before I posted. That's what I get for starting a response and moving on to something else before submitting 🙂
January 29, 2016 at 2:02 pm
That was it.
Not sure why I didn't see that.
Thanks,
Tom
January 30, 2016 at 4:03 am
Though you have your answer, I'd like to add that in many cases it is not needed to nest CASE expression. For instance, this should work for you as well:
CASE WHEN ISNULL(oa.IsRetail,odr. IsRetail) <> 1
THEN <whatever it should return if the ISNULL evaluates to anything other than 1>
WHEN DealerID = 5 THEN 5
ELSE 10
END
January 30, 2016 at 9:45 am
Hugo Kornelis (1/30/2016)
Though you have your answer, I'd like to add that in many cases it is not needed to nest CASE expression. For instance, this should work for you as well:
CASE WHEN ISNULL(oa.IsRetail,odr. IsRetail) <> 1THEN <whatever it should return if the ISNULL evaluates to anything other than 1>
WHEN DealerID = 5 THEN 5
ELSE 10
END
Personally I'd embed CASEs before I reverted to negative conditions, since they're so much less intuitive to follow.
CASE WHEN ISNULL(oa.IsRetail,odr. IsRetail) = 1
THEN CASE WHEN DealerID = 5 THEN 5 ELSE 10 END
ELSE NULL END --"ELSE NULL" is optional, of course
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 30, 2016 at 12:57 pm
I didn't know there was a return value for isnull.
In most of the samples on line that is not even mentioned. But I did see a few that talked about it, just need to look closer at it.
I always thought if it was null, it would be the replacement value, same with coalesce.
Thanks,
Tom
January 30, 2016 at 1:14 pm
tshad (1/30/2016)
I didn't know there was a return value for isnull.In most of the samples on line that is not even mentioned. But I did see a few that talked about it, just need to look closer at it.
I always thought if it was null, it would be the replacement value, same with coalesce.
Thanks,
Tom
There isn't a return value from the function in and of itself if that is what you mean. The return value is based on your inputs. Note, if both inputs evaluate to NULL then NULL is returned.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 30, 2016 at 2:08 pm
The return value of a function is the literally just value that it returns when run.
SELECT ISNULL(1, 2) -- returns 1, because the first parameter is not null
SELECT ISNULL(NULL,5) -- returns 5 because the first parameter is null.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply