[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Alternative to cursor

Posted on 2008-10-03
4
Medium Priority
?
737 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

650 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