Nested Case Error when run from a remote server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Nested Case Error when run from a remote server

Author

Topic

MegaTrain
Starting Member

16 Posts

Posted - 2006-11-28 : 13:12:30

I have a denormalized sales table with separate columns for each month of sales. My query needs to select the appropriate month based on the parameter passed in, and it seems to work with a case statement.

The Stored PROC is on a SQL 2000 server, operating on views located in a different SQL 2005 server.

A view called vCustbyRep has AccountNumber, Brand, and Rep (SalesRepID). vCustActual has AccountNumber and 12 columns of sales figures, Curr_1 to Curr_12.

This one worked:

SELECT c.Brand, SUBSTRING(c.AccountNumber, 2, 1) AS CustType,
MTDSales = CASE @MonthNum
WHEN 1 THEN SUM(A.Curr_1)
WHEN 2 THEN SUM(A.Curr_2)
WHEN 3 THEN SUM(A.Curr_3)
WHEN 4 THEN SUM(A.Curr_4)
WHEN 5 THEN SUM(A.Curr_5)
WHEN 6 THEN SUM(A.Curr_6)
WHEN 7 THEN SUM(A.Curr_7)
WHEN 8 THEN SUM(A.Curr_8)
WHEN 9 THEN SUM(A.Curr_9)
WHEN 10 THEN SUM(A.Curr_10)
WHEN 11 THEN SUM(A.Curr_11)
ELSE SUM(A.Curr_12)
END
FROM Server6.DBOne.DBO.vCustbyRep c INNER JOIN
Server6.DBOne.DBO.vCustActual a ON c.AccountNumber = a.AccountNumber
WHERE (c.REP = @Rep)
GROUP BY c.Brand, SUBSTRING(c.AccountNumber, 2, 1)

But if I remove the CustType field (the second character of the Account number is an encoded customer type), it gives me the following error:

Msg 8180, Level 16, State 1, Line 7
Statement(s) could not be prepared.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'Qry1035'.
Msg 125, Level 15, State 4, Line 7
Case expressions may only be nested to level 10.

Particularly annoying because this isn't a NESTED case expression at all--just a case statement with 12 cases. Note that this query works fine when executed directly on the SQL 2005 server.

My guess: the original one worked because the complex GROUP BY substring thing forced it to push all data across to the destination server anyway, but once it tried to execute a more optimized version, it somehow didn't like the conditions being passed over.

So... How do I fix it?
Any better way of selecting the proper column given the correct month that could help me eliminate the CASE statement?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 13:17:46

Did you also remove the field from the GROUP BY statement?

SELECT		c.Brand,
		SUBSTRING(c.AccountNumber, 2, 1) AS CustType,
		SUM(
			CASE @MonthNum
				WHEN 1 THEN a.Curr_1
				WHEN 2 THEN a.Curr_2
				WHEN 3 THEN a.Curr_3
				WHEN 4 THEN a.Curr_4
				WHEN 5 THEN a.Curr_5
				WHEN 6 THEN a.Curr_6
				WHEN 7 THEN a.Curr_7
				WHEN 8 THEN a.Curr_8
				WHEN 9 THEN a.Curr_9
				WHEN 10 THEN a.Curr_10
				WHEN 11 THEN a.Curr_11
				WHEN 12 THEN A.Curr_12
				ELSE 0
			END
		) MTDSales
FROM		Server6.DBOne.DBO.vCustbyRep c
INNER JOIN	Server6.DBOne.DBO.vCustActual a ON c.AccountNumber = a.AccountNumber
WHERE		c.Rep = @Rep
GROUP BY	c.Brand,
		SUBSTRING(c.AccountNumber, 2, 1)

SELECT		c.Brand,
		SUM(
			CASE @MonthNum
				WHEN 1 THEN a.Curr_1
				WHEN 2 THEN a.Curr_2
				WHEN 3 THEN a.Curr_3
				WHEN 4 THEN a.Curr_4
				WHEN 5 THEN a.Curr_5
				WHEN 6 THEN a.Curr_6
				WHEN 7 THEN a.Curr_7
				WHEN 8 THEN a.Curr_8
				WHEN 9 THEN a.Curr_9
				WHEN 10 THEN a.Curr_10
				WHEN 11 THEN a.Curr_11
				WHEN 12 THEN A.Curr_12
				ELSE 0
			END
		) MTDSales
FROM		Server6.DBOne.DBO.vCustbyRep c
INNER JOIN	Server6.DBOne.DBO.vCustActual a ON c.AccountNumber = a.AccountNumber
WHERE		c.Rep = @Rep
GROUP BY	c.Brand

Peter Larsson
Helsingborg, Sweden

MegaTrain
Starting Member

16 Posts

Posted - 2006-11-28 : 13:21:05

yep.
Actually it works if I leave it IN the Group By, but it errors without it. (And I don't really want it grouped by that)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-11-28 : 13:29:01

So, you've denormalized I suppose to make reporting easier, yet of course you've made it much harder.

*Summarizing* data helps simplify and speed up reporting, but denormalizing rarely does, in my experience -- especially when you do it in this manner (i.e., cross-tabbing). This SELECT would be trivial to write if your data was stored properly.

- Jeff

MegaTrain
Starting Member

16 Posts

Posted - 2006-11-28 : 13:40:46

Totally agree with you Jeff, but I'm not the creator of the table design in this case, so that's not my call.

I actually had tried to create a normalized view so I could report on this easier, but the performance was about 5x as slow as the Case statement. It looked something like:

Select AccountNumber, 1 as Month,  Curr_1 as Sales From vCustActual
UNION ALL
Select AccountNumber, 2 as Month,  Curr_2 as Sales From vCustActual
UNION ALL
Select AccountNumber, 3 as Month,  Curr_3 as Sales From vCustActual
...

Reporting off this one was very easy, but again, it just took too long to run.

any other ideas? Any info out there about this specific error message?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-28 : 13:43:04

As far as I know this is just a (disappointing) limitation of the CASE statement, you cannot have more than 10 WHENs if it is running on a linked server.

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75005

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-11-28 : 14:10:28

Based on the names -- vCustbyRep , vCustActual -- it looks like you are querying views. Is this correct? Can you instead query the base tables that hopefully haven't been denormalized yet?

- Jeff

MegaTrain
Starting Member

16 Posts

Posted - 2006-11-28 : 14:59:00

No, the normalization is in the base tables. The view is simply to ignore a couple of dozen other irrelevant columns, give me some more natural column names, and to extract (and group-by) the 8-digit account number from a longer string field.

MegaTrain
Starting Member

16 Posts

Posted - 2006-11-28 : 17:57:10

Since my underlying data source is populated nightly, not real-time, I ended up just making a scheduled job to group, summarize, and push data to some staging tables. That let me create a normalized version of the data, and revise the queries appropriately.

Now the whole stored proc executes in <1 sec, where before it was easily running 25-35 seconds.

mtrade488
Starting Member

1 Post

Posted - 2008-03-29 : 10:05:08

You can get around this limitation by using a temp table.

SELECT
CASE
WHEN ...
WHEN ...
.
.
.
.
.
.
.
.
END
INTO #temp
FROM linkedserver........

SELECT *
FROM #temp

Halotron
Starting Member

1 Post

Posted - 2008-10-17 : 17:23:42

You could also process it as several individual case statements summed together:

MTDSales = CASE @MonthNum WHEN 1 THEN SUM(A.Curr_1) ELSE 0 END
+ CASE @MonthNum WHEN 2 THEN SUM(A.Curr_2) ELSE 0 END
+ CASE @MonthNum WHEN 3 THEN SUM(A.Curr_3) ELSE 0 END
+ CASE @MonthNum WHEN 4 THEN SUM(A.Curr_4) ELSE 0 END
+ CASE @MonthNum WHEN 5 THEN SUM(A.Curr_5) ELSE 0 END
+ CASE @MonthNum WHEN 6 THEN SUM(A.Curr_6) ELSE 0 END
+ CASE @MonthNum WHEN 7 THEN SUM(A.Curr_7) ELSE 0 END
+ CASE @MonthNum WHEN 8 THEN SUM(A.Curr_8) ELSE 0 END
+ CASE @MonthNum WHEN 9 THEN SUM(A.Curr_9) ELSE 0 END
+ CASE @MonthNum WHEN 10 THEN SUM(A.Curr_10) ELSE 0 END
+ CASE @MonthNum WHEN 11 THEN SUM(A.Curr_11) ELSE 0 END
+ CASE @MonthNum WHEN 12 THEN SUM(A.Curr_12) ELSE 0 END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources