Case Statements nesting more than 10 levels

  • 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

  • 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

  • Can you post the query you're using together with the exact error message?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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

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



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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

  • I am an end user. Where can I find the version infoprmation you request?

  • 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

  • Edition ProductVersionProductLevel

    Developer Edition10.0.2531.0SP1

    Thanks for your help

  • 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

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

    Converting oxygen into carbon dioxide, since 1955.
  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply