locked
Case expressions may only be nested to level 10. RRS feed

  • Question

  • Hi All

    I am having a frustrating issue and not sure how to solve..

    I have a query which works absolutely fine when connecting to an actual server:

    				WITH CLAIMDATA AS(
    				SELECT DISTINCT
    				DB_NAME() AS DBName,
    				'UA' AS Client,
    				POLICY AS KMPONO,
    				EVENTCODE,
    				CONVERT(Varchar,POPHID)+'-'+CONVERT(Varchar,POPSID)+'-'+CONVERT(Varchar,POLICY)+'-'+CONVERT(Varchar,CO.SEQ)+'-'+CONVERT(Varchar,CO.[YEAR])+'-'+CONVERT(Varchar,UP.POEDNO) + '-'+CONVERT(Varchar,CO.[DEC]) AS [PolicyREF],
    				CONVERT(Varchar,POPHID)+'-'+CONVERT(Varchar,POPSID)+'-'+CONVERT(Varchar,POLICY)+'-'+CONVERT(Varchar,CO.SEQ)+'-'+CONVERT(Varchar,CO.[YEAR])+'-'+CONVERT(Varchar,UP.POEDNO) + '-'+CONVERT(Varchar,CO.[DEC])+'-'+CONVERT(Varchar,CO.CLAIM) AS [ClaimREF],
    				CONVERT(Varchar,CO.COMPANY)+'-'+CONVERT(Varchar,CO.POLICY)+'-'+CONVERT(Varchar,CO.[SEQ])+'-'+CONVERT(Varchar,CO.[YEAR])+'-'+Convert(Varchar,CO.Claim) AS [ClaimREF_CC],
    				[YEAR] AS [POUGYR],
    				CASE
    				WHEN (CO.majorloss IN ('IMP','hh','lead','nihl',
                                                 'cdt','tob','hiv','emf',
                                                 'ltx'))
                     THEN ('Other Latent')
                     WHEN (CO.majorloss = 'ENV' and CO.poqdfg = 'D')
                     THEN ('Direct Environmental')
                     WHEN (CO.majorloss = 'ENV' and CO.poqdfg = 'R')
                     THEN ('R/I Environmental')
                     WHEN (CO.majorloss = 'ASB' and CO.poqdfg = 'D')
                     THEN ('Direct Asbestos')
                     WHEN (CO.majorloss = 'ASB' and CO.poqdfg = 'R')
                     THEN ('R/I Asbestos')
                     WHEN (CO.origref = '83115160AA')
                     THEN ('UA 83115160AA')
                     WHEN (CO.eventcode LIKE '010911%')
                     THEN ('Catastrophe')
                     WHEN (CO.YEAR <= 1985 and CO.poqdfg = 'D')
                     THEN ('General Casualty - Direct All Other')
    
                     WHEN (CO.YEAR <= 1985 and CO.poqdfg = 'R')
                     THEN ('General Casualty - R/I All Other')
                     WHEN (CO.YEAR > 1985
                           AND CO.origref LIKE '195027%'
                            OR CO.YEAR > 1985
                               AND CO.origref LIKE '195032%'
                            OR CO.YEAR > 1985
                               AND CO.origref LIKE '001312%'
                            OR CO.YEAR > 1985
                               AND CO.origref LIKE '002373%'
                            OR CO.YEAR > 1985
                               AND CO.origref LIKE '002375%')
                     THEN ('Construction Defect')
    
    				 ELSE 
    				 Convert(varchar,UP.podpcd) --DepartmentCode
    				 + '|'
    				 + Convert(varchar,UP.POQDFG) --Direct/Reinsurance
    				 + '|'
    				 + Convert(varchar,UP.PODPDV) --DepartmentDivision
    				 + '|'
    				 + Convert(varchar,UP.POCBCD) --ClassCode
    				 END as ReservingKey
    				From SYS_MNTH_MAR15_UA.dbo.COG626A CO
    				INNER JOIN SYS_MNTH_MAR15_UA.dbo.UPPOREP UP
    				ON CO.POLICY = UP.POPONO AND Co.[SEQ] = UP.POPOSQ AND CO.[YEAR] = UP.POUGYR AND CO.[DEC] = UP.PODANO
    				),
    				CC AS(
    				 Select DBName, Client, KMPONO, POUGYR, PolicyRef, ClaimRef, EventCode,
    				 CASE 
    				 WHEN ClaimREF_CC in('1-142772-1-1978-1','1-142926-1-1978-2','1-142956-1-1981-2','1-143278-1-1975-2','1-143279-1-1975-2','1-150204-1-1982-1','1-150205-1-1983-1','1-150512-1-1980-1','1-150513-1-1980-1','1-150663-1-1976-1','1-150664-1-1976-1','8-850251-1-1972-1','8-850251-1-1973-1','8-850251-1-1974-1','8-850251-1-1975-1','8-850251-1-1976-1','8-850251-1-1977-1','8-850251-1-1978-1','8-850251-1-1979-1','8-850251-1-1980-1','8-850251-1-1981-1','8-850251-1-1982-1','8-850251-1-1983-1','8-850251-1-1984-1','8-850289-1-1976-1','8-850289-1-1977-1') THEN 'Direct Asbestos'
    				 WHEN ClaimREF_CC in('1-150203-1-1980-1','1-150206-1-1984-1','1-150208-1-1985-1','1-150507-1-1979-1','1-150665-1-1973-1','1-150666-1-1973-1','8-850289-1-1974-1','8-850289-1-1975-1') THEN 'Direct Environmental'
    				 WHEN ClaimREF_CC in('1-142458-1-1979-13','1-142458-1-1979-14','1-142458-1-1979-16','1-143042-1-1978-14','1-143042-1-1978-15','1-143042-1-1978-18','1-143052-1-1976-12','1-143052-1-1976-13','1-143053-1-1976-12','1-143073-1-1974-8','1-143078-1-1976-7','1-143142-1-1977-15','1-143142-1-1977-17','1-143142-1-1977-18','1-143142-1-1977-19','1-143143-1-1977-15','1-143143-1-1977-17','1-143143-1-1977-18','1-143143-1-1977-19','1-147864-1-1979-4','1-147864-1-1979-5','1-147864-1-1979-7','1-150234-1-1979-1','1-150760-1-1979-1','8-800035-1-1972-23','8-800035-1-1972-30','8-800035-1-1972-33','8-800035-1-1972-39','8-800035-1-1973-31','8-800035-1-1973-45','8-800035-1-1973-46','8-800035-1-1973-51','8-800035-1-1974-61','8-800035-1-1974-84','8-800035-1-1974-92','8-800097-1-1972-0','8-800097-1-1973-0','8-800097-1-1974-0','8-800097-1-1975-0','8-800097-1-1976-0','8-800097-1-1977-0','8-800097-1-1978-0','8-800097-1-1979-0','8-800097-1-1979-36','8-800098-1-1979-0','8-800176-1-1976-10','8-802020-1-1975-30','8-802020-1-1975-35','8-802020-1-1976-30','8-802020-1-1976-33','8-802020-1-1976-36','8-802020-1-1977-27','8-802020-1-1977-32','8-802020-1-1977-33','8-802020-1-1978-23','8-802020-1-1978-25','8-802020-1-1978-31','8-802020-1-1979-21','8-802020-1-1979-24','8-802020-1-1979-30','8-802020-1-1980-24','8-802020-1-1980-27','8-802020-1-1980-31','8-802021-1-1976-21','8-802021-1-1976-22','8-802021-1-1977-24','8-802021-1-1977-25','8-802021-1-1977-26','8-802021-1-1978-20','8-802021-1-1978-21','8-802021-1-1978-24','8-802021-1-1979-15','8-802021-1-1979-16','8-802021-1-1979-18','8-802021-1-1980-13','8-802021-1-1980-14','8-802021-1-1980-17','8-802113-1-1980-0','8-802714-1-1979-0','8-804623-1-1980-9','8-805123-1-1980-0','8-809690-1-1980-0','8-809926-1-1982-40','8-809998-1-1980-3','8-809998-1-1980-4','8-810267-1-1984-2','8-810366-1-1980-0','8-810366-1-1982-0','8-810366-1-1983-0','8-810413-1-1981-0','8-810645-1-1981-14','8-810645-1-1981-15','8-810645-1-1981-16','8-810645-1-1981-18','8-810645-1-1982-20','8-810645-1-1982-21','8-810645-1-1983-16','8-810645-1-1983-20','8-810645-1-1984-15','8-810645-1-1985-13','8-810754-1-1981-25','8-810754-1-1981-27','8-810754-1-1981-30','8-810754-1-1982-39','8-810754-1-1982-41','8-810754-1-1982-45','8-810754-1-1983-24','8-810754-1-1983-26','8-810754-1-1983-32','8-810754-1-1984-27','8-810754-1-1984-29','8-810754-1-1984-35','8-810754-1-1985-15','8-810756-1-1984-5','8-810756-1-1984-8','8-810758-1-1982-9','8-810758-1-1983-6','8-810771-1-1981-0','8-810771-1-1982-0','8-810774-1-1982-0','8-811107-1-1983-0','9-919396-1-1980-67','9-919424-1-1981-17','9-920386-1-1984-10','9-920386-1-1984-12','9-920386-1-1984-13','9-929842-1-1980-0','9-929842-1-1981-0','9-929992-1-1983-0','9-930947-1-1983-1','9-930947-1-1983-8','9-930947-1-1983-9') THEN 'R/I Asbestos'
    				 WHEN ClaimREF_CC in('8-800021-1-1978-0','8-811107-1-1982-0','9-929992-1-1984-0') THEN 'R/I Environmental'
    				 ELSE ReservingKey
    				 END as ReservingKey
    
    				  from Claimdata)
    
    				  Select DBName, Client, KMPONO, POUGYR, Policyref, ClaimRef, 
    				  ReservingKey + 
    				  CASE 
    				  WHEN EVENTCODE in('82030113','86040109','85030111','85010180','SK80125','84030127','000ASV','89010117','82070108','840301CA','81030111','80030106','79030104','000ASZ','000ASY','000ASX','000ASW','83030114','SK79139') THEN '|ALLIED'
    				  WHEN EVENTCODE in('79010124','A"5','A"4','A"3','A"2','850401AA','85040101','SK75040','830401CB','830401AE','81120904','840401AB','SK77068','A"6','SK78052','82062501','78010112','77010120','76010132','75120301','75010116','74070601','73070601','72070601','00ABWZ','00ABWX','00ABWV','SK79066','800401AC','S5615','S1982','S6258','S1110','S6524','A"7','S5944','S1493','SK39741','S6715','S0423','SK39752','ABW9','A"X','A"W','A"8','SK39731') THEN '|BABCOCK'
    				  WHEN EVENTCODE in('78010127','77010140','S1178','SK77102','73110103','S1049','80110106','79110105','76110104','SK76104','75110107','81030108','S6003','72110102','00ADRZ','00ADRY','SK78133','SK79021','SK54731','74110104','SK54751','S3120','S0908','89091202','86092301','SK54761','ADR2','ADR3','SK6027','SK74065','84110115','SK74068','SK76098','ADR4','S0594','81110107','SK54741','82110111','SK75095','ADR6','ADR5','83110116','SK75092') THEN '|DRESSER'
    				  WHEN EVENTCODE in('89032402') THEN '|EXXON'
    				  WHEN EVENTCODE in('90080207','90080203','90080204','90080202','90080206','90080208','90080209','90080210','90080205') THEN '|KUWAIT'
    				  WHEN EVENTCODE in('01091109','01091122','01091101','01091102','01091103','01091104','01091105','01091106','01091107','01091108','01091118','01091129','01091128','01091127','01091126','01091125','01091124','01091123','01091120','01091119','01091110','01091117','01091116','01091115','01091114','01091113','01091112','01091111','01091121') THEN '|WTC'
    				  ELSE '' END AS ReservingKey
    				   from CC

    When i execute this I get the exact resultset I want.

    If i change the connector to REPLPROD (which is a linked server):

    From REPLPROD.XUNMDTAUAI.dbo.UPPOREP UP INNER JOIN REPLPROD.XUNMDTAUAI.dbo.UKKMREP UK

    I get the error:

    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.

    Does anyone know how i can solve this problem? As far as i can see, I have not nested anything

    Sorry for no test tables - if you need them let me know and i will try and get some insert statements

    Wednesday, August 5, 2015 9:24 AM

Answers

  • Overlooking the limitation of the CASE expression, the code is need of refactoring anyway. Put all those list of values in a mapping table:

    INSERT @tbl
       VALUES ('1-142772-1-1978-1', 'Direct Asbestos'),
              ('1-142926-1-1978-2), 'Direct Asbestos'),
              ...
              ('1-150203-1-1980-1', 'Direct Environmental'),
              ...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Wednesday, August 5, 2015 4:01 PM
    • Marked as answer by Eric__Zhang Friday, August 14, 2015 1:53 AM
    Wednesday, August 5, 2015 12:00 PM

All replies

  • This is a limit with the Linker server, you cannot have more that 10 levels in a CASE statements while using Linked Server.

    You can try using this query within an SP or a UDF, then execute via Linked Server, may work.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Wednesday, August 5, 2015 9:40 AM
  • Also you have a repeated which is wrong logic in your CASE statement:

    WHEN (CO.YEAR > 1985
                           AND CO.origref LIKE '195027%'
                            OR CO.YEAR > 1985
                               AND CO.origref LIKE '195032%'
                            OR CO.YEAR > 1985
                               AND CO.origref LIKE '001312%'
                            OR CO.YEAR > 1985
                               AND CO.origref LIKE '002373%'
                            OR CO.YEAR > 1985
                               AND CO.origref LIKE '002375%')
                     THEN ('Construction Defect')

    Why have you repeated same "OR CO.Year > 1985" condition several times? Is the Year different and you by mistake places 1985 at all places?

    If not, then remove all other occurrences.

    If yes, then you need to have Year & Origref within in brackets, like:

    WHEN (CO.YEAR > 1985
                           AND CO.origref LIKE '195027%')
                            OR (CO.YEAR > 1986
                               AND CO.origref LIKE '195032%')
                            OR (CO.YEAR > 1987
                               AND CO.origref LIKE '001312%')
                            OR (CO.YEAR > 1988
                               AND CO.origref LIKE '002373%')
                            OR (CO.YEAR > 1989
                               AND CO.origref LIKE '002375%')
                     THEN ('Construction Defect')


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Wednesday, August 5, 2015 9:50 AM
  • Hi Manoj

    In isolation, I can execute the contents of the "ClaimData" CTE

    The "CC" cte only has 6

    and at the end - in my selection criteria the case statement only has 4 entries

    Could the sql engine in the back be adding them up somehow?

    Wednesday, August 5, 2015 10:15 AM
  • SQL Optimizer will finally expand the CTE, just like a VIEW and add up the CASES into one.

    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Wednesday, August 5, 2015 10:19 AM
  • So how to fix?
    Wednesday, August 5, 2015 10:33 AM
  • Overlooking the limitation of the CASE expression, the code is need of refactoring anyway. Put all those list of values in a mapping table:

    INSERT @tbl
       VALUES ('1-142772-1-1978-1', 'Direct Asbestos'),
              ('1-142926-1-1978-2), 'Direct Asbestos'),
              ...
              ('1-150203-1-1980-1', 'Direct Environmental'),
              ...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Wednesday, August 5, 2015 4:01 PM
    • Marked as answer by Eric__Zhang Friday, August 14, 2015 1:53 AM
    Wednesday, August 5, 2015 12:00 PM
  • As also mentioned by @Erland, you can create a mapping table and join it with you base table.

    Or, as I mentioned earlier, create an SP with the CASE statement query.


    ~manoj | email: http://scr.im/m22g
    http://SQLwithManoj.com
    MCCA 2011 | My FB Page

    Wednesday, August 5, 2015 12:54 PM