Nested too deeply

Question

Nested too deeply

Deepthy

SSCommitted

Points: 1823
More actions
October 27, 2010 at 4:57 am

#242250

Hi ,i have 180 nested if statements and when i run function i get this:
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
what can be done in this case?

Viewing 10 posts - 1 through 9 (of 9 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you could seperate them into different queries, try and do the IFS in steps and save the results to a table in between steps.

might run a bit quicker as well.

Deepthy SSCommitted Points: 1823 More actions · Answer 2

Deepthy

SSCommitted

Points: 1823

October 27, 2010 at 5:19 am

#1242768

Thank you steve. I'll try it

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Deepthy (10/27/2010)
Hi ,i have 180 nested if statements and when i run function i get this:
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
what can be done in this case?

Can you post all or part of it?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Deepthy SSCommitted Points: 1823 More actions · Answer 4

Hello chris,

this is a part of my function

CREATE function [dbo].[PostcodeValidation](@Country NVARCHAR(100),@Addressline NVARCHAR(100))

RETURNS VARCHAR(10)

AS

BEGIN

DECLARE @Result varchar(10)

IF @COUNTRY IN ('Afghanistan','Albania','Angola','Antarctica','Antigua And Barbuda','Aruba','Bahamas','Belize','Bhutan','Botswana',

'Burkina Faso','Burundi','Cameroon','Central African Republic','Comoros','Cook Islands','Cote DIvoire','Democratic Peoples Republic Of Korea',

'Democratic Republic Of Congo','Djibouti','Dominica','East Timor','Equatorial Guinea','Eritrea','Ethiopia','Fiji','French Southern Territories',

'Gambia','Ghana','Gibraltar','Grenada','Guinea','Guyana','Hong Kong','Ireland','Jamaica','Kiribati','Macau','Malawi','Mali','Mauritania',

'Mauritius','Montserrat','Namibia','Nauru','Netherlands Antilles','Niue','Palestinian Territory','Panama','Peoples Republic Of Congo',

'Qatar','Republic Of Benin','Rwanda','Saint Kitts And Nevis','Saint Lucia','Saint Vincent And The Grenadines','Sao Tome And Principe',

'Seychelles','Sierra Leone','Solomon Islands','Somalia','Suriname','Syrian Arab Republic','Togo','Tokelau','Tonga','Trinidad And Tobago',

'Tuvalu','Uganda','United Arab Emirates','United Republic Of Tanzania','Vanuatu','Western Sahara','Yemen','Zimbabwe')

SET @RESULT = NULL

---------------------- Albania POSTCODE VALIDATION ----------------

ELSE IF @COUNTRY = 'Albania'

set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('%[0123456789][0123456789][0123456789][0123456789] %', ' ' + @addressline + ' '), 0), 4))

--------------------Algeria POSTCODE VALIDATION ------------------

ELSE IF @COUNTRY = 'Algeria'

set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('%[0123456789][0123456789][0123456789][0123456789][0123456789] %', ' ' + @addressline + ' '), 0), 5))

--------------------American Samoa POSTCODE VALIDATION ---------------------

ELSE IF @COUNTRY = 'American Samoa'

set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('%[0123456789][0123456789][0123456789][0123456789][0123456789] %', ' ' + @addressline + ' '), 0), 5))

---------------ANDORRA POSTCODE VALIDATION---------------------------------

ELSE IF @COUNTRY = 'Andorra'

set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('% AD[0123456789][0123456789][0123456789] %', ' ' + @addressline + ' '), 0), 5))

-------------- ANGUILLA postcode validation---------------------------------------

ELSE IF @COUNTRY = 'Anguilla'

set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('% AI-2640 %', ' ' + @addressline + ' '), 0), 7))

--------------ARGENTINA POSTCODE VALIDATION------------------------------------

ELSE IF @COUNTRY = 'Argentina'

set @Result=COALESCE(

(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [ABCDEFGHIJKLMNOPQRSTUVWYZ][0123456789][0123456789][0123456789][0123456789][ABCDEFGHIJKLMNOPQRSTUVWYZ][ABCDEFGHIJKLMNOPQRSTUVWYZ][ABCDEFGHIJKLMNOPQRSTUVWYZ] %', ' ' + @addressline + ' '), 0), 8)),

(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [0123456789][0123456789][0123456789][0123456789] %', ' ' + @addressline + ' '), 0), 4)))

-------------- ARMENIA postcode validation---------------------------------------

ELSE IF @COUNTRY = 'Armenia'

set @Result=COALESCE(

(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [0123456789][0123456789][0123456789][0123456789] %', ' ' + @addressline + ' '), 0), 4)),

(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [0123456789][0123456789][0123456789][0123456789][0123456789][0123456789] %', ' ' + @addressline + ' '), 0), 6)))

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

Thanks for posting.

Here's a handy reference for you:

http://msdn.microsoft.com/en-us/library/ms187489.aspx

Which would make your code much more compact for the next step:

---------------------- Albania POSTCODE VALIDATION ----------------

ELSE IF @COUNTRY = 'Albania'

set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [0-9][0-9][0-9][0-9] %', ' ' + @addressline + ' '), 0), 4))

--------------------Algeria POSTCODE VALIDATION ------------------

ELSE IF @COUNTRY = 'Algeria'

set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [0-9][0-9][0-9][0-9][0-9] %', ' ' + @addressline + ' '), 0), 5))

--------------------American Samoa POSTCODE VALIDATION ---------------------

ELSE IF @COUNTRY = 'American Samoa'

set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [0-9][0-9][0-9][0-9][0-9] %', ' ' + @addressline + ' '), 0), 5))

---------------ANDORRA POSTCODE VALIDATION---------------------------------

ELSE IF @COUNTRY = 'Andorra'

set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('% AD[0-9][0-9][0-9] %', ' ' + @addressline + ' '), 0), 5))

-------------- ANGUILLA postcode validation---------------------------------------

ELSE IF @COUNTRY = 'Anguilla'

set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('% AI-2640 %', ' ' + @addressline + ' '), 0), 7))

--------------ARGENTINA POSTCODE VALIDATION------------------------------------

ELSE IF @COUNTRY = 'Argentina'

set @Result=COALESCE(

(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [A-Z][0-9][0-9][0-9][0-9][A-Z][A-Z][A-Z] %', ' ' + @addressline + ' '), 0), 8)),

(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [0-9][0-9][0-9] %', ' ' + @addressline + ' '), 0), 4)))

If I were you, I'd create a table containing Country (as PK) with a couple of extra columns containing the patterns to be matched. Argentina would look like this:

Country Pattern1 Pattern2

Argentina [A-Z][0-9][0-9][0-9][0-9][A-Z][A-Z][A-Z] [0-9][0-9][0-9]

Your query would run something like this:

SELECT COALESCE(

(SUBSTRING(@ADDRESSLINE, nullif(patindex('% ' + f.Pattern1 + ' %', ' ' + @addressline + ' '), 0), 8)),

(SUBSTRING(@ADDRESSLINE, nullif(patindex('% ' + f.Pattern2 + ' %', ' ' + @addressline + ' '), 0), 4)))

FROM CountryPostcodeFormats f

WHERE f.Country = @COUNTRY

You could of course normalise this to give one row per country per pattern.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Deepthy SSCommitted Points: 1823 More actions · Answer 6

Deepthy

SSCommitted

Points: 1823

October 27, 2010 at 6:15 am

#1242803

Thanks Chris

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 7

You're welcome.

If you choose to go down the table route, you could "encapsulate" the code within a (CROSS) APPLY to provide a very fast solution.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

ChrisM@home (10/27/2010)
Thanks for posting.
Here's a handy reference for you:
http://msdn.microsoft.com/en-us/library/ms187489.aspx
Which would make your code much more compact for the next step:
---------------------- Albania POSTCODE VALIDATION ----------------
ELSE IF @COUNTRY = 'Albania'
set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [0-9][0-9][0-9][0-9] %', ' ' + @addressline + ' '), 0), 4))
--------------------Algeria POSTCODE VALIDATION ------------------
ELSE IF @COUNTRY = 'Algeria'
set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [0-9][0-9][0-9][0-9][0-9] %', ' ' + @addressline + ' '), 0), 5))
--------------------American Samoa POSTCODE VALIDATION ---------------------
ELSE IF @COUNTRY = 'American Samoa'
set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [0-9][0-9][0-9][0-9][0-9] %', ' ' + @addressline + ' '), 0), 5))
---------------ANDORRA POSTCODE VALIDATION---------------------------------
ELSE IF @COUNTRY = 'Andorra'
set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('% AD[0-9][0-9][0-9] %', ' ' + @addressline + ' '), 0), 5))
-------------- ANGUILLA postcode validation---------------------------------------
ELSE IF @COUNTRY = 'Anguilla'
set @Result=(SUBSTRING(@ADDRESSLINE, nullif(patindex('% AI-2640 %', ' ' + @addressline + ' '), 0), 7))
--------------ARGENTINA POSTCODE VALIDATION------------------------------------
ELSE IF @COUNTRY = 'Argentina'
set @Result=COALESCE(
(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [A-Z][0-9][0-9][0-9][0-9][A-Z][A-Z][A-Z] %', ' ' + @addressline + ' '), 0), 8)),
(SUBSTRING(@ADDRESSLINE, nullif(patindex('% [0-9][0-9][0-9] %', ' ' + @addressline + ' '), 0), 4)))
If I were you, I'd create a table containing Country (as PK) with a couple of extra columns containing the patterns to be matched. Argentina would look like this:
Country Pattern1 Pattern2
Argentina [A-Z][0-9][0-9][0-9][0-9][A-Z][A-Z][A-Z] [0-9][0-9][0-9]
Your query would run something like this:
SELECT COALESCE(
(SUBSTRING(@ADDRESSLINE, nullif(patindex('% ' + f.Pattern1 + ' %', ' ' + @addressline + ' '), 0), 8)),
(SUBSTRING(@ADDRESSLINE, nullif(patindex('% ' + f.Pattern2 + ' %', ' ' + @addressline + ' '), 0), 4)))
FROM CountryPostcodeFormats f
WHERE f.Country = @COUNTRY
You could of course normalise this to give one row per country per pattern.

Just a suggestion... CASE/WHEN would work much better for this even if there weren't a good and proper lookup table (which was an excellent suggestion BTW).

--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)
Intro to Tally Tables and Functions

Deepthy SSCommitted Points: 1823 More actions · Answer 9

Thanks for that Jeff.

Is there any way of achieving something like this

If country table is inserted with a another new pattern for a country , then the function should be altered with the newly added pattern automatically.

Any help is really appreciated