Nesting Error for CASE Statement that Isn't Nested

Question

Nesting Error for CASE Statement that Isn't Nested

jkalmar 43328

Say Hey Kid

Points: 693
More actions
September 19, 2013 at 10:59 am

#282678

Hello all,
I'm getting an odd set of errors when trying to run the following:
declare @FY1 char(4)
set @FY1 = (DATEPART(yy,DATEADD(m,-2,GETDATE()) ))
declare @fy char(2)
set @fy = SUBSTRING(@fy1,3,2)
declare @MonthUnits char (2)
set @MonthUnits = datepart(mm, dateadd(mm, -2, getdate()))
select '000' + ltrim(MCMCU)
, CASE @MonthUnits
WHEN 1 THEN GBAN01/100 -- January
WHEN 2 THEN GBAN02/100 -- February
WHEN 3 THEN GBAN03/100 -- March
WHEN 4 THEN GBAN04/100 -- April
WHEN 5 THEN GBAN05/100 -- May
WHEN 6 THEN GBAN06/100 -- June
WHEN 7 THEN GBAN07/100 -- July
WHEN 8 THEN GBAN08/100 -- August
WHEN 9 THEN GBAN09/100 -- September
WHEN 10 THEN GBAN10/100 -- October
WHEN 11 THEN GBAN11/100 -- November
WHEN 12 THEN GBAN12/100 -- December
END
, mcdl02
from [JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0902,
[JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0006
where GBMCU = MCMCU and
GBFY = @fy and GBLT = 'AU' and
GBOBJ = '9900' and GBSUB = '006' and
MCSTYL in ('R') AND MCMCU<' 999' AND
MCRP22<>'X'
Here's the error message:
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Qry1043'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Qry1016'.
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
Apart from the fact that the CASE isn't nested, what's odd about this is that if I create a temp table, use this code as part of an INSERT to populate that table, then SELECT from the temp table, everything runs just fine. I've been Googling for over an hour and I can't figure out why. Any help will be appreciated!
EDIT: If I comment out any two of the WHEN...THEN lines it runs fine. It really thinks those are nested CASE statements - but they're not. Weird...
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

I have run into this in the past. The error message makes no sense but you can't have more than 10 when clauses in a case expression when querying over a link server.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jkalmar 43328 Say Hey Kid Points: 693 More actions · Answer 2

I finally found an article that explained that the parser expands all CASE statements, which is why the linked server sees this as nested. I ended up splitting it into two queries, one with 10 CASE statements and one with the other two, and used UNION ALL. It worked, but it's klugey.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Does it fails if you change the syntax like this?

CASE

WHEN @MonthUnits = 1 THEN GBAN01/100 -- January

WHEN @MonthUnits = 2 THEN GBAN02/100 -- February

WHEN @MonthUnits = 3 THEN GBAN03/100 -- March

WHEN @MonthUnits = 4 THEN GBAN04/100 -- April

WHEN @MonthUnits = 5 THEN GBAN05/100 -- May

WHEN @MonthUnits = 6 THEN GBAN06/100 -- June

WHEN @MonthUnits = 7 THEN GBAN07/100 -- July

WHEN @MonthUnits = 8 THEN GBAN08/100 -- August

WHEN @MonthUnits = 9 THEN GBAN09/100 -- September

WHEN @MonthUnits = 10 THEN GBAN10/100 -- October

WHEN @MonthUnits = 11 THEN GBAN11/100 -- November

WHEN @MonthUnits = 12 THEN GBAN12/100 -- December

END

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

jkalmar 43328 (9/19/2013)
I finally found an article that explained that the parser expands all CASE statements, which is why the linked server sees this as nested. I ended up splitting it into two queries, one with 10 CASE statements and one with the other two, and used UNION ALL. It worked, but it's klugey.

Maybe you could make it a proc or even a view on the remote server instead?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

You could easily turn this into a view on your remote server. There is no need for any variables here.

select '000' + ltrim(MCMCU)

, CASE datepart(month, dateadd(month, -2, getdate()))

WHEN 1 THEN GBAN01/100 -- January

WHEN 2 THEN GBAN02/100 -- February

WHEN 3 THEN GBAN03/100 -- March

WHEN 4 THEN GBAN04/100 -- April

WHEN 5 THEN GBAN05/100 -- May

WHEN 6 THEN GBAN06/100 -- June

WHEN 7 THEN GBAN07/100 -- July

WHEN 8 THEN GBAN08/100 -- August

WHEN 9 THEN GBAN09/100 -- September

WHEN 10 THEN GBAN10/100 -- October

WHEN 11 THEN GBAN11/100 -- November

WHEN 12 THEN GBAN12/100 -- December

END

, mcdl02

from PRODDTA.F0902

inner join PRODDTA.F0006 on GBMCU = MCMCU

where GBFY = right(cast(DATEPART(year, DATEADD(month, -2, GETDATE())) as CHAR(4)), 2)

and GBLT = 'AU'

and GBOBJ = '9900'

and GBSUB = '006'

and MCSTYL = 'R'

AND MCMCU < ' 999'

AND MCRP22 <> 'X'

I did modify your dateadd functions a bit. I much prefer to use the whole name instead of the abbreviation. It adds clarity and you don't have to go to the internet to figure what code to use. 😉

--EDIT--

And I changed your join to use the ANSI-92 style instead of the older ANSI-89 style.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 101105 More actions · Answer 6

As another kludge, you could probably do this:

COALESCE(

CASE WHEN @MonthUnits = 1 THEN GBAN01 ELSE NULL END,

CASE WHEN @MonthUnits = 2 THEN GBAN02 ELSE NULL END,

CASE WHEN @MonthUnits = 3 THEN GBAN03 ELSE NULL END,

CASE WHEN @MonthUnits = 4 THEN GBAN04 ELSE NULL END,

CASE WHEN @MonthUnits = 5 THEN GBAN05 ELSE NULL END,

CASE WHEN @MonthUnits = 6 THEN GBAN06 ELSE NULL END,

CASE WHEN @MonthUnits = 7 THEN GBAN07 ELSE NULL END,

CASE WHEN @MonthUnits = 8 THEN GBAN08 ELSE NULL END,

CASE WHEN @MonthUnits = 9 THEN GBAN09 ELSE NULL END,

CASE WHEN @MonthUnits =10 THEN GBAN10 ELSE NULL END,

CASE WHEN @MonthUnits =11 THEN GBAN11 ELSE NULL END,

CASE WHEN @MonthUnits =12 THEN GBAN12 ELSE NULL END)

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".

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Ooh if kludges are what we want here, I love kludges...

select months.val

from some_table

cross apply (

select 1 ,GBAN01/100 union all

select 2 ,GBAN02/100 union all

select 3 ,GBAN03/100 union all

select 4 ,GBAN04/100 union all

select 5 ,GBAN05/100 union all

select 6 ,GBAN06/100 union all

select 7 ,GBAN07/100 union all

select 8 ,GBAN08/100 union all

select 9 ,GBAN09/100 union all

select 10,GBAN10/100 union all

select 11,GBAN11/100 union all

select 12,GBAN12/100

) months(ind,val)

where months.ind=@MonthUnits

This construct seems to be slightly slower (about 29ms out of <500ms) on a 1M row test table than the CASE method and uses a lot more cpu, but the same reads...so...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);