April 5, 2012 at 11:18 pm
Say I have a remodeling business. I track the labor and material charges by address. I have a field called lastmonthworked that shows the last month/year worked at that address. Each of the remaining fields is named for a year/month ande contain the charges for that time period. What I would like to do is to pull the address, last year/month worked at that address and the amount of charges the last month/yeart worked.
Columns and data:
Address 123 Main
Lastmonthworked 201009
201001 $100
201002 $ 0
201003 $ 50
201004
201005
201006
201007
201008
201009 $300
201010
201011
201012
201101
select address,lastmonthworked,
case
when lastmonthworked = '201001' then 201001
when lastmonthworked = '201002' then 201002
when lastmonthworked = '201003' then 201003
when lastmonthworked = '201004' then 201004
when lastmonthworked = '201005' then 201005
when lastmonthworked = '201006' then 201006
when lastmonthworked = '201007' then 201007
when lastmonthworked = '201008' then 201008
when lastmonthworked = '201009' then 201009
when lastmonthworked = '201010' then 201010
when lastmonthworked = '201011' then 201011
when lastmonthworked = '201012' then 201012
when lastmonthworked = '201101' then 201101
else 0 end;as Lastmonthamount
from table
I am having problems running this query since there are more than 10 when statements. I get the error that says I cannot run more than 10 nested levels. This is a sql server table and I have a co-worker uses sql server against a teradata table and has queries with a lot more than 10 when statements. Can you tell me how I can do what I want to? Thanks
April 6, 2012 at 12:21 am
Here is an example of a case statement (that I know works) that contains more than 10 when statements
http://reportingservicestnt.blogspot.com/2010/10/subscriptions-sql.html
The nesting level problem you are encountering is not with the case statement.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2012 at 2:56 am
Can you post the query you're using together with the exact error message?
April 6, 2012 at 6:59 am
Thanks for the responses. Here is the error message and code.
When I comment out all but 10 when statements,it works.
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
Here is the code:
select
wo ,
last_chg,
case
when last_chg='2009001' then amt_0109
when last_chg='2009002' then amt_0209
when last_chg='2009003' then amt_0309
when last_chg='2009004' then amt_0409
when last_chg='2009005' then amt_0509
when last_chg='2009006' then amt_0609
when last_chg='2009007' then amt_0709
when last_chg='2009008' then amt_0809
when last_chg='2009009' then amt_0909
when last_chg='2009010' then amt_1009
when last_chg='2009011' then amt_1109
when last_chg='2009012' then amt_1209
when last_chg='2010001' then amt_0110
when last_chg='2010002' then amt_0210
when last_chg='2010003' then amt_0310
when last_chg='2010004' then amt_0410
when last_chg='2010005' then amt_0510
when last_chg='2010006' then amt_0610
when last_chg='2010007' then amt_0710
when last_chg='2010008' then amt_0810
when last_chg='2010009' then amt_0910
when last_chg='2010010' then amt_1010
when last_chg='2010011' then amt_1110
when last_chg='2010012' then amt_1210
when last_chg='2012001' then amt_0112
when last_chg='2012002' then amt_0212
when last_chg='2012003' then amt_0312
when last_chg='2012004' then amt_0412
when last_chg='2012005' then amt_0512
when last_chg='2012006' then amt_0612
when last_chg='2012007' then amt_0712
when last_chg='2012008' then amt_0812
when last_chg='2012009' then amt_0912
when last_chg='2012010' then amt_1012
when last_chg='2012011' then amt_1112
when last_chg='2012012' then amt_1212
else 0 end as last_amt
from tablename
April 6, 2012 at 7:04 am
It seems like you're dealing with a denormalized table. I'd probably use UNPIVOT to get values for amt_* into one column and then use a simple join.
April 6, 2012 at 8:29 am
Even with that, the case statement you provided is not nested.
What Version and SP level is your SQL Instance?
A nested case (where 10 levels is the limitation) looks like:
case when then blah
else case when then blah
else case when then blah
....
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2012 at 10:16 am
I am an end user. Where can I find the version infoprmation you request?
April 6, 2012 at 10:19 am
run this query
select serverproperty('edition') as Edition
,serverproperty('productversion') as ProductVersion
,serverproperty('productlevel') as ProductLevel
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2012 at 10:35 am
Edition ProductVersionProductLevel
Developer Edition10.0.2531.0SP1
Thanks for your help
April 6, 2012 at 10:48 am
K - something still says that the query you posted is not the entire case statement as it is being run on your system.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 9, 2012 at 12:39 pm
Jason is correct, there is no error when I run this code:
CREATE TABLE #tablename (wo int, last_chg varchar(7),
amt_0109 varchar(7),
amt_0209 varchar(7),
amt_0309 varchar(7),
amt_0409 varchar(7),
amt_0509 varchar(7),
amt_0609 varchar(7),
amt_0709 varchar(7),
amt_0809 varchar(7),
amt_0909 varchar(7),
amt_1009 varchar(7),
amt_1109 varchar(7),
amt_1209 varchar(7),
amt_0110 varchar(7),
amt_0210 varchar(7),
amt_0310 varchar(7),
amt_0410 varchar(7),
amt_0510 varchar(7),
amt_0610 varchar(7),
amt_0710 varchar(7),
amt_0810 varchar(7),
amt_0910 varchar(7),
amt_1010 varchar(7),
amt_1110 varchar(7),
amt_1210 varchar(7),
amt_0112 varchar(7),
amt_0212 varchar(7),
amt_0312 varchar(7),
amt_0412 varchar(7),
amt_0512 varchar(7),
amt_0612 varchar(7),
amt_0712 varchar(7),
amt_0812 varchar(7),
amt_0912 varchar(7),
amt_1012 varchar(7),
amt_1112 varchar(7),
amt_1212 varchar(7)
)
select
wo ,
last_chg,
case
when last_chg='2009001' then amt_0109
when last_chg='2009002' then amt_0209
when last_chg='2009003' then amt_0309
when last_chg='2009004' then amt_0409
when last_chg='2009005' then amt_0509
when last_chg='2009006' then amt_0609
when last_chg='2009007' then amt_0709
when last_chg='2009008' then amt_0809
when last_chg='2009009' then amt_0909
when last_chg='2009010' then amt_1009
when last_chg='2009011' then amt_1109
when last_chg='2009012' then amt_1209
when last_chg='2010001' then amt_0110
when last_chg='2010002' then amt_0210
when last_chg='2010003' then amt_0310
when last_chg='2010004' then amt_0410
when last_chg='2010005' then amt_0510
when last_chg='2010006' then amt_0610
when last_chg='2010007' then amt_0710
when last_chg='2010008' then amt_0810
when last_chg='2010009' then amt_0910
when last_chg='2010010' then amt_1010
when last_chg='2010011' then amt_1110
when last_chg='2010012' then amt_1210
when last_chg='2012001' then amt_0112
when last_chg='2012002' then amt_0212
when last_chg='2012003' then amt_0312
when last_chg='2012004' then amt_0412
when last_chg='2012005' then amt_0512
when last_chg='2012006' then amt_0612
when last_chg='2012007' then amt_0712
when last_chg='2012008' then amt_0812
when last_chg='2012009' then amt_0912
when last_chg='2012010' then amt_1012
when last_chg='2012011' then amt_1112
when last_chg='2012012' then amt_1212
else 0 end as last_amt
from #tablename
There is no nesting going on here, so there must be something else afoot. Please post your DDL and sample data inserts.
April 9, 2012 at 4:52 pm
A big thank you to everyone who looked at this and responded back A collegue of mine found out that I had a problem and was able to solve it.
It appears there may be a limitation to the number of case statements executed within a Linked Server (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75005). While the data is on one server, I am running it from a linked server
April 9, 2012 at 6:29 pm
Create the proc on the linked server and then execute it from there.
It would also be a much better thing if someone were to properly normalize that table
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply