Cursor using update and order

195 pts.
Tags:
RPG
RPGLE
SQL
I'd like to declare a cursor to traverse a file in keyed order, updating some records It looks like I can do one or the other, but not both, that is I can set up the cursor with the order phrase or the update phrase buy not both. Is there a reason for this limitation? ec/exec sql c+ declare xxxxCursor Cursor c+ for c+ select * c+ from rlgd c+ where name = ' ' c+ for update c+ with nc c/end-exec
ASKED: December 22, 2007  10:17 PM
UPDATED: December 24, 2007  3:08 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Your basic SQL Cursor syntax would look something like this.

<pre>DECLARE @Col1 int, @Col2 int
DECLARE cur CURSOR FOR SELECT Col1, Col2 FROM Table WHERE Col3 = 12
OPEN cur
FETCH NEXT FROM cur into @Col1, @Col2
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Table2
SET Col5 = @Col2
WHERE Col2 = @Col1

FETCH NEXT FROM cur into @Col1, @Col2
END
CLOSE cur
DEALLOCATE cur</pre>

Based on the bit of code you provided a straight update statement is what you need. There doesn’t appear to be any reason to use a cursor.

UPDATE Table
SET Col1 = SourceTable.Col4
FROM SourceTable
WHERE Table.Col3 = SourceTable.Col5

SQL Server will process data faster in bulk than it will row by row. The optimizer is setup to work this way.

Thanks, but that doesn’t really help or answer the qeustion. I’d like to travesse the file in a certain order:
for update
c/exec sql
c+ declare xxxxCursor Cursor
c+ for
c+ select x2, x3
c+ from file
c+ where field1 > ‘ ‘
c+ for update
c+ order by field2, field3
c/end-exec
This stmt is not permitted.
The compiler will allow the “order by” phrase or the “update” phrase but not both. Any ideas wy or how to get around it?

mrdenny, thanks again for the help, I’ve determined the problem. Basically, when you specify the ORDER phrase you’re creating a set of key values that can’t be changed, so the UPDATE phrase has to explicitly not include the keys. That is, you can’t use UPDATE, you have to use UPDATE OF and then list the columns to be updated and you have to exclude the keys.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Unknowingone
    [...] Check it out! While looking through the blogosphere we stumbled on an interesting post today.Here’s a quick excerptNew Answer. by Mrdenny. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following