Fetch Next with Fetch Single

[rcandeo]

I have a loop like this one:

select F56001
fetch next F56001
While status = success
fetch single F56001 (another key)
If status = success
update F56001
else
insert F56001
end if
fetch next F56001
end while

My report is not working as expected, I think the reason is the fetch single with another key in the middlee of the while.
Does someone can help me with a tip to repointer my key?
I´ve tried to put a new select before the last fetch next but it is in loop because of that.

 

[DBohner-(db)]

Hmmm... Nested Select Statements.... You might get bit by this one...
depending on how your DB is looking at things.

What is your database?...

You might consider adding the second key - to the first key sequence of the
first select?

Just a thought.

Daniel
[email protected]
www.existinglight.net
AS/400 SP 18 XE



Daniel Bohner
[email protected]
www.existinglight.net
JDE - XE, SP 18 & AS/400 V4R5.0
JDE - B7331 & MS SQL 7x

 

[rcandeo]

Re: RE: Fetch Next with Fetch Single

Daniel, my database is DB2/400. Can you be more clearly? I didn´t understand your answer.
Thank You

 

[barrier]

Robson,

You should open a second instance of the F56001 table using a handle. Use this instance to do the fetch single.

There was a recent post (by Larry Jones, I think) that describes how to set up handles.

B.

 

[rcandeo]

Thank You Barrier,
I think I can go on with this now.
Regards.

 

[Ben_Britain]

I have had success with similar ER logic, as long as the fetchsingle, insert and update all use the alternate index.

I would assume that the fetchsingle is grabbing a record different than the record grabbed in the fetchnext. If they are one and the same, you don't really need the fetchsingle.

Ben again

 

[BBritain]

Re: RE: Fetch Next with Fetch Single

I just came across a small gotcha when working with 'mixed' Table I/O. I have several times had the need to repeat the code (with both FetchSingle and FetchNext commands) and had it not work, only to find out that I forgot to change the indexes. The printed ER will usually give you some clue but now whenever I use Table IO using any index other than the default index - I will put a comment above it indicating which index I used. This has saved me lots of work (and not commenting has cost me lots of work). This is especially critical when someone else is maintaining your code.

Just an Afterthought,
Ben again

 

[peter_hamilton]

There is an easier way to do this, than use Handles. The code would look like this:
Note: That one is the View, the other is the file.
Note2: Always do the read before the update on the file being updated in this sort of loop, even if you are updating the same record.

select V56001
fetch next V56001
While status = success
fetch single F56001 (another key)
If status = success
update F56001
else
insert F56001
end if
fetch next V56001
end while