View previous topic :: View next topic
|
Author |
Message |
hcl_ln
New User
Joined: 14 Sep 2005 Posts: 21
|
|
|
|
My SQL query is like this. (declare)
Code: |
EXEC SQL
DECLARE CRSR CURSOR WITH HOLD FOR
SELECT A.col1,A.col2,A.col3,A.col4
FROM table1 as A
WHERE cond1 and cond2
END-EXEC |
Knowing that it returns multiple rows from my FETCH (after open cursor), is there a way just to return one row at a time, do some processing in my cobol pgm; return to fetch for the second row and then do the processing; return to fetch for the thrid row and do the processing; until all the rows from the fetch are done.
I don't know FETCH is that smart to process one record at a time.
Also, I cannot put more conditions in my WHERE clause to strip off the records.
Alternatively, I could do this way.
Unload the table with the above SQL into a flat file and read it in my cobol program. This seems simpler, but the requirement is to do with a cursor in the program.
Thanks for your time, |
|
Back to top |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 Location: Spain
|
|
|
|
but the requirement is to do with a cursor in the program.
the requirement is a Cursor becouse that's the 'normal' way.
you could use FETCH FIRST ROW ONLY ...but that implies you should modify your WHERE clause.
you talk about unloading..
DECLARE CURSOR-------------------->FD YOURFILE
OPEN CURSOR------------------------->OPEN INPUT YOURFILE
FETCH----------------------------------->READ
CLOSE CURSOR------------------------>CLOSE YOURFILE |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
Quote: |
...multiple rows from my FETCH (after open cursor), is there a way just to return one row at a time, d |
I believe that there is a little bit of confusion in the terminology..
when the select returns "ONE" row ... OK for the simple select
The "CURSOR technique is/MUST_BE used when a SELECT might return more than one ROW...
The FETCH statement WILL retrieve one row at the time from the rows
selected by the cursor definition ...
... and YES... FETCH is smart because it was made just for that |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1042 Location: Richmond, Virginia
|
|
|
|
In other words, simplified:
Look at the cursor as a sequential file and the FETCH as a READ NEXT, with AT END checking done by referencing the SQL code.. |
|
Back to top |
|
|
|