Solved

Alternative to cursor

Posted on 2008-10-03
4
734 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 250 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

726 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