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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy