Solved

Alternative to cursor

Posted on 2008-10-03
4
728 Views
Last Modified: 2012-08-13
I would like to eliminate the cursor from the script below.

I need to check every canadian zip code from the table coordonnee
and update the field CodepostalID with the codepostalID from the table codepostal.

To achieve this I check the zip code
if is there I take the codepostalid
if not I check the zip code with the first 5 characters
if is there I take the codepostalid
if not I check the zip code with the first 4 characters
if is there I take the codepostalid
if not I check the zip code with the first 3 characters
if is not there I assign a null value

But after reading a lot of awful things about cursors, I would really like to eliminate them.

thank you for the help.

Table Coordonnee

                 CodePostal varchar(10),
            CodePostalID INT,
            MunicipaliteID INT,  
            TerritoireClscID INT,
            RegionRssID INT,
            ProvinceID INT,
            PaysID INT,
            DVK_Temp INT
     



TABLE CodePostal

      CodePostalID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
      Code VARCHAR(10) NOT NULL,
      PaysID INT NOT NULL




DECLARE @CUR CURSOR

SET @CUR= CURSOR FOR
                  SELECT COORDONNEEID,REPLACE(CODEPOSTAL,' ','') AS CODEPOSTAL
                  FROM COORDONNEE
                  
                  

DECLARE @COODID VARCHAR(15)
DECLARE @CODE VARCHAR(10)
DECLARE @ID INT


OPEN @CUR
FETCH NEXT FROM @CUR INTO @COODID,@CODE
WHILE @@FETCH_STATUS=0

BEGIN
            SET @ID=(SELECT CODEPOSTALID FROM CODEPOSTAL
            WHERE CODE = @CODE)
      
            IF @ID IS NULL
                  BEGIN
                  SET @ID=(SELECT CODEPOSTALID FROM CODEPOSTAL
                  WHERE CODE = SUBSTRING(@CODE,1,5))

            IF @ID IS NULL
                     BEGIN
                     SET @ID=(SELECT CODEPOSTALID FROM CODEPOSTAL
                               WHERE CODE = SUBSTRING(@CODE,1,4))

            IF @ID IS NULL
            BEGIN
            SET @ID=(SELECT CODEPOSTALID FROM CODEPOSTAL
                        WHERE CODE = SUBSTRING(@CODE,1,3))
                                                                  
            IF @ID IS NULL
            BEGIN
            UPDATE COORDONNEE SET CodePostalid= NULL  WHERE COORDONNEEID = @COODID      
            FETCH NEXT FROM @CUR INTO @COODID,@CODE
            END

            ELSE
            BEGIN
            UPDATE COORDONNEE SET CodePostalid=@ID  WHERE COORDONNEEID = @COODID
            FETCH NEXT FROM @CUR INTO @COODID,@CODE
            END
            END

            ELSE
             BEGIN
             UPDATE COORDONNEE SET CodePostalid=@ID  WHERE COORDONNEEID = @COODID
             FETCH NEXT FROM @CUR INTO @COODID,@CODE
             END
                                          
            END

            ELSE
            BEGIN
            UPDATE COORDONNEE SET CodePostalid=@ID  WHERE COORDONNEEID = @COODID
            FETCH NEXT FROM @CUR INTO @COODID,@CODE
            END
                        
            END
      
            ELSE
            BEGIN
            UPDATE COORDONNEE SET CodePostalid=@ID  WHERE COORDONNEEID = @COODID
            FETCH NEXT FROM @CUR INTO @COODID,@CODE
            END
END
0
Comment
Question by:l-ipse
  • 2
  • 2
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
try this

update t1
set CodepostalID = (select CodepostalID from (
select CodepostalID, row_number() over(order by CODE desc) as rown
from codepostal t2
where CODE = REPLACE(CODEPOSTAL,' ','')
or CODE = SUBSTRING(REPLACE(CODEPOSTAL,' ',''),1,5))
or CODE = SUBSTRING(REPLACE(CODEPOSTAL,' ',''),1,4))
or CODE = SUBSTRING(REPLACE(CODEPOSTAL,' ',''),1,3))
) t where rown = 1)
 from coordonnee t1
0
 

Author Comment

by:l-ipse
Comment Utility
Thank you very much for yor response.

When I run this query I got this error message

Msg 156, Niveau 15, État 1, Ligne 7
Incorrect syntax near the keyword 'or'.

and I don't know how to fix it.

thanx
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 250 total points
Comment Utility
update t1
set CodepostalID = (select CodepostalID from (
select CodepostalID, row_number() over(order by CODE desc) as rown
from codepostal t2
where CODE = REPLACE(CODEPOSTAL,' ','')
or CODE = SUBSTRING(REPLACE(CODEPOSTAL,' ',''),1,5)
or CODE = SUBSTRING(REPLACE(CODEPOSTAL,' ',''),1,4)
or CODE = SUBSTRING(REPLACE(CODEPOSTAL,' ',''),1,3)
) t where rown = 1)
 from coordonnee t1
0
 

Author Closing Comment

by:l-ipse
Comment Utility
Thank you very, your answer work perfectly!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now