Case expressions may only be nested to level 10

Question

I have the following code for handling phone numbers such as country code for Australia +61, 61, 001161 etc. The problem that I have I can't insert any CASE statement anymore under: CASE WHEN LEFT(@BPartyNo, 4) = '+610'

It said that Case expressions may only be nested to level 10

How do I streamline this TSQL so I can put more CASE?

USE [TelcoStage_PROD]
GO
/****** Object:  UserDefinedFunction [dbo].[ufn_stg_ProperBPartyNoExtra]    Script Date: 07/12/2010 15:27:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--=====================================================================================================================
-- OBJECT NAME          : dbo.ufn_stg_ProperBPartyNoExtra
-- INPUTS               : @BPartyNo 
-- OUTPUTS              : VARCHAR(32)
-- RETURN CODES         : N/A
-- DEPENDENCIES         : N/A
-- DESCRIPTION          : This function is used to get the extra after 10 character (MNET or S)
--
-- EXAMPLES (optional)  : N/A
--
-- HISTORY:
-- #-----------------------------------------------------------------------------------------------------------------
-- # DATE       | VERSION        | MODIFIED BY | DESCRIPTION
-- #-----------------------------------------------------------------------------------------------------------------
====================================================================================================================


ALTER FUNCTION [dbo].[ufn_stg_ProperBPartyNoExtra](@BPartyNo AS VARCHAR(MAX))RETURNS VARCHAR(32)
AS
BEGIN   
    DECLARE @Return VARCHAR(32);

    SET @Return = '';

    IF (LEN(@BPartyNo) > 0) 
        SELECT @Return = CASE WHEN LEFT(@BPartyNo, 4) = '+610' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 5, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 5, LEN(@BPartyNo)) ) ) ELSE
                                    CASE WHEN LEFT(@BPartyNo, 3) = '+61' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 4, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 4, LEN(@BPartyNo)) ) ) ELSE
                                        CASE WHEN LEFT(@BPartyNo, 2) = '61' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 3, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 3, LEN(@BPartyNo)) ) ) ELSE       
                                            CASE WHEN LEFT(@BPartyNo, 6) = '001161' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 7, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 7, LEN(@BPartyNo)) ) ) ELSE 
                                                CASE WHEN ( LEFT(@BPartyNo,2) = '01' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo)) ELSE 
                                                    CASE WHEN ( LEFT(@BPartyNo,2) = '02' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo)) ELSE 
                                                        CASE WHEN ( LEFT(@BPartyNo,2) = '03' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo)) ELSE 
                                                            CASE WHEN ( LEFT(@BPartyNo,2) = '04' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo)) ELSE 
                                                                CASE WHEN ( LEFT(@BPartyNo,2) = '07' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo)) ELSE 
                                                                    CASE WHEN ( LEFT(@BPartyNo,2) = '08' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo)) ELSE '' END
                                                                END
                                                            END
                                                        END
                                                    END
                                                END
                                            END
                                        END
                                    END
                        END;
    ELSE
        SELECT @Return = '';

    RETURN @Return
END

score 23 · Accepted Answer · answered Jul 12 '10 at 06:05

They don't need to be nested at all:

   SELECT @Return = CASE WHEN LEFT(@BPartyNo, 4) = '+610' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 5, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 5, LEN(@BPartyNo)) ) )
                         WHEN LEFT(@BPartyNo, 3) = '+61' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 4, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 4, LEN(@BPartyNo)) ) )
                         WHEN LEFT(@BPartyNo, 2) = '61' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 3, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 3, LEN(@BPartyNo)) ) )
                         WHEN LEFT(@BPartyNo, 6) = '001161' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 7, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 7, LEN(@BPartyNo)) ) )
                         WHEN ( LEFT(@BPartyNo,2) = '01' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
                         WHEN ( LEFT(@BPartyNo,2) = '02' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
                         WHEN ( LEFT(@BPartyNo,2) = '03' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
                         WHEN ( LEFT(@BPartyNo,2) = '04' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
                         WHEN ( LEFT(@BPartyNo,2) = '07' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
                         WHEN ( LEFT(@BPartyNo,2) = '08' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
                         ELSE ''
                     END

Personally, I would restructure your code so that you make sure the data in sanitized upon input, rather than trying to sanitize it now (when it's clearly too late...). Or at least do the conversion in your client language (i.e. in whatever is calling this sproc), which is hopefully more suited to the task of string manipulation than T-SQL is.

'codeka' Harding: Thanks for this. Didn't realise I don't have nested. I have to do at this level for string manipulation due to client is actually SQL as well which ETL process. — dcpartners, Jul 12 '10 at 06:15

score 11 · Answer 2 · edited Apr 17 '19 at 11:04

11

You don't need to nest the case statements, you can have many WHEN ... THEN

CASE 
    WHEN @x = 1 THEN 1 
    WHEN @x = 2 THEN 2 
    WHEN @x = 3 THEN 3 
    ELSE 4 
END

edited Apr 17 '19 at 11:04

Community

1
1

answered Jul 12 '10 at 06:01

Chris Diver

19,362
4
47
58

This doesn't works for more than 10 `WHEN THEN` parts when you are selecting from a table. Oddly, it does work when you're using `SELECT` without `FROM` – BdR Nov 26 '20 at 16:48

score 3 · Answer 3 · edited May 16 '19 at 14:31

I had written 12 nested IIF statements for a view which errored as a Nested Case Error. I was taking 12 columns (Jan - Dec) which held numeric values. The view was to convert each month to a row. I then realized I could split the nested IIFs into two groups of six, and add them together! It worked!

select AP.Year, AP.Period, RPA.Company, RPA.Contract, RPA.Description, RPA.PM, RPA.ProjectManager,
IIF(AP.Period=1, RPA.JanNetBilled, IIF(AP.Period=2, RPA.FebNetBilled, IIF(AP.Period=3, RPA.MarNetBilled, IIF(AP.Period=4, RPA.AprNetBilled, IIF(AP.Period=5, RPA.MayNetBilled, 
IIF(AP.Period=6, RPA.JunNetBilled, 0)))))) +
IIF(AP.Period=7, RPA.JulNetBilled, IIF(AP.Period=8, RPA.AugNetBilled, IIF(AP.Period=9, RPA.SepNetBilled, IIF(AP.Period=10, RPA.OctNetBilled, 
IIF(AP.Period=11, RPA.NovNetBilled, IIF(AP.Period=12, RPA.DecNetBilled, 0)))))) as BilledAmt
from AccountPeriod AP
INNER JOIN REVENUE_PROJECTION_ANALYSIS RPA ON RPA.YEAR = AP.Year

PretoriaCoder · Answer 4 · 2014-06-02T17:34:00.620

This is a workaround for the nested case scenario, not reccommended. Have your first 9 CASES in a Coalesce, then end it with a else null and put the next cases in the next block.

 COALESCE ( (CASE  WHEN TRY1 = 1 THEN TRY1    
 ELSE CASE WHEN TRY2 = 1 THEN TRY2   
ELSE CASE WHEN TRY3 = 1 THEN TRY3   
ELSE NULL    END END
 END),  

CASE WHEN TRY11 = 1 THEN TRY11   
ELSE CASE WHEN TRY12 = 1 THEN TRY12   
ELSE CASE WHEN TRY13 = 1 THEN TRY13   
ELSE NULL   END END END
 ),0)  AS MyValue

score 1 · Answer 5 · answered Nov 09 '19 at 23:53

Though SQL Server v17 allows more than 10 nested case expressions, it appears that previous versions don't allow them. I found out the hard way when I pasted a big case statement from a v17 server back to a v16 SQL Server environment. My solution was to build either a temporary or a virtual table and use it in a sub-query. E.g., I had to substitute the following code:

 if object_id( 'tempdb..#ILEDocType' ) is not null 
   drop table #ILEDocType ;

 create table #ILEDocType 
( [Code] [int] 
, [Name] [varchar](50) 
)
  ;

  insert into #ILEDocType 
  values 
                    ( 1 , 'Sales Shipment' )
                    , ( 2 , 'Sales Invoice' )
                    , ( 3 , 'Sales Return Receipt' )
                    , ( 4 , 'Sales Credit Memo' )
                    , ( 5 , 'Purchase Receipt' )
                    , ( 6 , 'Purchase Invoice' )
                    , ( 8 , 'Purchase Return Shipment' )
                    , ( 9 , 'Purchase Credit Memo' )
                    , ( 10 , 'Transfer Shipment' )
                    , ( 11 , 'Transfer Receipt' )
                    , ( 12 , 'Service Shipment' )
                    , ( 13 , 'Service Invoice' )
                    , ( 14 , 'Service Credit Memo' )
                    , ( 15 , 'Posted Assembly'  )
                    --else 'Other' 
    ;

select [Document Type Desc] = 
         isnull( ( select t.[Name] 
                     from #ILEDocType as t 
                         where t.[Code] = ile.[Document Type] ) , 'Other' ) 
    from dbo.[Item Ledger Entry] as ile 
  ;             

.. for the following code that gave the subject error: 

select 
 ( case ile.[Document Type] 
                    when 1 then 'Sales Shipment'
                    when 2 then 'Sales Invoice'
                    when 3 then 'Sales Return Receipt' 
                    when 4 then 'Sales Credit Memo'
                    when 5 then 'Purchase Receipt'
                    when 6 then 'Purchase Invoice'
                    when 8 then 'Purchase Return Shipment'
                    when 9 then 'Purchase Credit Memo'
                    when 10 then 'Transfer Shipment'
                    when 11 then 'Transfer Receipt'
                    when 12 then 'Service Shipment'
                    when 13 then 'Service Invoice'
                    when 14 then 'Service Credit Memo'
                    when 15 then 'Posted Assembly' 
                    else 'Other' end ) as [Document Type Desc]
   from dbo.[Item Ledger Entry] as ile 
   ;

luiscla27 · Answer 6 · 2023-02-02T15:39:10.410

In my case I didn't had any nested level.

The issue occurred only when executing the select statement from a LinkedServer, my query was similar to the following. Also, the issue didn't happened when it was executed directly in the linked server database:

SELECT case some_column 
    when '1' then '10'
    when '2' then '9'
    when '3' then '8'
    when '4' then '7'
    when '5' then '6'
    when '6' then '5'
    when '7' then '4'
    when '8' then '3'
    when '9' then '2'
    when '10' then '1'
    when '11' then '0'
    else '12' end some_alias
FROM [MY_LINKED_SERVER].[DATABASE_NAME].[SCHEMA].[TABLE]

The issue got fixed by using a different syntax for linked servers, this was the solution:

SELECT case some_column 
    when '1' then '10'
    when '2' then '9'
    when '3' then '8'
    when '4' then '7'
    when '5' then '6'
    when '6' then '5'
    when '7' then '4'
    when '8' then '3'
    when '9' then '2'
    when '10' then '1'
    when '11' then '0'
    else '12' end some_alias
FROM openquery([MY_LINKED_SERVER], 'select some_column from [DATABASE_NAME].[SCHEMA].[TABLE]')

It also works when putting the case inside the openquery sql parameter.

Case expressions may only be nested to level 10

6 Answers6

In my case I didn't had any nested level.

Linked