How to do CASE WHEN THEN CASE

Question

Post reply

How to do CASE WHEN THEN CASE

tshad

SSCertifiable

Points: 5905
More actions
January 29, 2016 at 12:46 pm

#399646

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

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 🙂

tshad SSCertifiable Points: 5905 More actions · Answer 4

That was it.

Not sure why I didn't see that.

Thanks,

Tom

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 5

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

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

ScottPletcher SSC Guru Points: 100952 More actions · Answer 6

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) <> 1
THEN <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) A socialist is someone who will give you the shirt off *someone else's* back.

tshad SSCertifiable Points: 5905 More actions · Answer 7

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 8

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass