Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Case expressions may only be nested to level 10

Tags:

sql

tsql

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
like image 821
dcpartners Avatar asked Jul 12 '10 05:07

dcpartners


People also ask

Can CASE statements be nested?

CASE statements can be nested just as IF statements can.

What are the two types of case expressions?

There are two types of CASE expressions: simple and searched. In simple CASE expressions, an expression is compared with a value. When a match is found, the specified action in the THEN clause is applied. If no match is found, the action in the ELSE clause is applied.

What is difference between CASE statement and CASE expression?

A CASE expression is very similar in form to a CASE statement and allows you to choose which of one or more expressions to evaluate. The result of a CASE expression is a single value, whereas the result of a CASE statement is the execution of a sequence of PL/SQL statements.


3 Answers

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.

like image 97
Dean Harding Avatar answered Nov 15 '22 12:11

Dean Harding


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
like image 21
halnwheels Avatar answered Nov 15 '22 12:11

halnwheels


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
like image 26
Chris Diver Avatar answered Nov 15 '22 13:11

Chris Diver