Solved

Alternative to cursor

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

740 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