Case expressions error
-
I have a linked server and i am trying to do an update. The stored proc have case statements. when ever i run the stored proc i get an error Case expressions may only be nested to level 10.
i have 11 case statements. if i comment out the last case statement it works fine.
The funny thing is if i run proc from another linked server it runs fine. example Both A and B servers are linked to C.
The proc runs fine on A but not on B.
-
An interesting question. I would like to know the answer, too.
-
Maybe we could help more if we had the query in question before our eyes!!
-
Actually there are 11 whens in the statement. i get an error Case expressions may only be nested to level 10.
Here is the Query. I have hardcodded some stuff. But u will get an idea.
Declare @usersemail varchar(50)
Declare @Status varchar(50)
Declare @location varchar(50)
SELECT
@Status=part_status,
@location=loc_region,
@usersemail= SUBSTRING(loc_bus_email,
NULLIF(CHARINDEX('@', loc_bus_email), 0) + 1,
LEN(loc_bus_email) - CHARINDEX('@', loc_bus_email) + 1)
FROM LOCATION INNER JOIN PARTNER ON LOCATION.part_id = PARTNER.part_id where PARTNER.part_id ='0000000'
set @status ='platinum'
set @location = 'southaus'
UPDATE A
SET RoleId = Case
when @Status = 'Silver' and (@location ='italy' or @location ='belgium' or @location ='uk') then 8
when @Status = 'Gold' and (@location ='italy' or @location ='belgium' or @location ='uk') then 4
when @Status = 'Platinum' and (@location ='italy' or @location ='belgium' or @location ='uk') then 3
when @Status = 'VAD' and (@location ='italy' or @location ='belgium' or @location ='uk') then 15
when @Status = 'Platinum' and (@location ='asia' or @location ='northaus' or @location ='southaus') then 10
when @Status = 'gold' and (@location ='asia' or @location ='northaus' or @location ='southaus') then 11
when @Status = 'silver' and (@location ='asia' or @location ='northaus' or @location ='southaus') then 12
when @Status = 'VAD' and (@location ='asia' or @location ='northaus' or @location ='southaus') then 16
when @Status = 'Platinum' and (@location ='can' or @location ='gl'or @location ='latinam') then 13
when @Status = 'gold' and (@location ='can' or @location ='gl' or @location ='latinam') then 14
when @Status = 'Stagnant' then 9
end
FROM
[my.server].hello.dbo.UserRoles As A
WHERE RoleId in (3,4,8,10,11,12,13,14,15,16,9)
and userid in (select UserID from [my.server].hello.dbo.users where USERNAME like '%' + @usersemail)
-
This works for me :
DECLARE @I AS INT
SET @I = 12
SELECT CASE
WHEN @I = 1 THEN 1
WHEN @I = 2 THEN 2
WHEN @I = 3 THEN 3
WHEN @I = 4 THEN 4
WHEN @I = 5 THEN 5
WHEN @I = 6 THEN 6
WHEN @I = 7 THEN 7
WHEN @I = 8 THEN 8
WHEN @I = 9 THEN 9
WHEN @I = 11 THEN 11
WHEN @I = 11 THEN 11
WHEN @I = 12 THEN 12
ELSE @I
END
have you tried running your query on a local server instead of remote... Do you have a trigger that could create the nesting error. Or are you very deep in nesting levels for other reasons??
-
Do you think it would help if you changed this line:
when @Status = 'Stagnant' then 9
to:
Else 9
-
I might solve the compile error. But it would also change the logic of the case statement. I'm not sure it could work for him at the moment, and for the whole life of the project.
-
I thought the original question is for nested.
-
Me too but I can only work with the queries I'm presented with!!!
Well not quite but I got other things to do ATM
. -
I'm aware of the error message but this isn't REALLY a case of nested case statements but one of a lot of conditions. I wonder what would happen if you decided to actually make it nested...
SET RoleId = case when @Status = 'Silver' then case when ... when @Status = 'Gold' then case when ... when @Status = 'Platinum' then case when ... when @Status = 'VAD' then case when ... when @Status = 'Stagnant' then 9 end(Just curious. If you get a chance to test this let me know.)
-
How about this :
SELECTCASE WHEN 0 = 1 THEN 1 ELSE
CASE WHEN 1 = 2 THEN 2 ELSE
CASE WHEN 2 = 3 THEN 3 ELSE
CASE WHEN 3 = 4 THEN 4 ELSE
CASE WHEN 4 = 5 THEN 5 ELSE
CASE WHEN 5 = 6 THEN 6 ELSE
CASE WHEN 6 = 7 THEN 7 ELSE
CASE WHEN 7 = 8 THEN 8 ELSE
CASE WHEN 8 = 9 THEN 9 ELSE
CASE WHEN 10 = 11 THEN 10 ELSE 99
END END END END END END END END END END AS CaseTest
--99
SELECT
CASE WHEN 0 = 1 THEN 1 ELSE
CASE WHEN 1 = 2 THEN 2 ELSE
CASE WHEN 2 = 3 THEN 3 ELSE
CASE WHEN 3 = 4 THEN 4 ELSE
CASE WHEN 4 = 5 THEN 5 ELSE
CASE WHEN 5 = 6 THEN 6 ELSE
CASE WHEN 6 = 7 THEN 7 ELSE
CASE WHEN 7 = 8 THEN 8 ELSE
CASE WHEN 8 = 9 THEN 9 ELSE
CASE WHEN 10 = 11 THEN 10 ELSE
CASE WHEN 11 = 12 THEN 11 ELSE 9999
END END END END END END END END END END END AS CaseTest
--Case expressions may only be nested to level 10.
-
That would have given it an outright depth of 10.
My example should technically have only a depth of 2.
Like this:
SELECT CASE WHEN 11 = 12 THEN CASE WHEN 1 = 2 THEN 1 WHEN 2 = 3 THEN 2 END WHEN 12 = 13 THEN CASE WHEN 3 = 4 THEN 3 WHEN 4 = 5 THEN 4 WHEN 5 = 6 THEN 5 END WHEN 13 = 14 THEN CASE WHEN 6 = 7 THEN 6 WHEN 7 = 8 THEN 7 WHEN 8 = 9 THEN 9 END WHEN 14 = 15 THEN CASE WHEN 9 = 10 THEN 10 WHEN 10 = 11 THEN 11 END WHEN 15 = 16 THEN 12 ELSE 9999 END AS CaseTest--9999
-
Recheck my code. There are 10 ends in a row in the first query and it works.
Then I add another nested case (11th) and it won't even compile.
-
Check mine.
I'm saying "Don't do that!".
If you distribute the load correctly you can probably stay under 10.
CASE WHEN (11=12) OR (12=13) THEN CASE WHEN (11=12) THEN 1111 WHEN (12=13) THEN 2222 END -
Oh I C. Well that's becoming a moo point now since we don't have a failing code from the poster. There's not much more we can say at this point!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply