?
Solved

Alternative to cursor

Posted on 2008-10-03
4
Medium Priority
?
735 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22633845
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
ID: 22634858
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 1000 total points
ID: 22634941
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
ID: 31502738
Thank you very, your answer work perfectly!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

801 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