Fetch relative not working

[Jharriman]

I normally use a FETCH NEXT to read multiple rows into a data structure to process into my subfile and let the system handle PageUp. I wanted to try this same thing using a page at a time subfile and had problems. My FETCH RELATIVE statement is not working like everything I read says it should. It is acting just like a FETCH PRIOR. I used a SCROLL CURSOR and even a DYNAMIC SCROLL CURSOR. This pgm will still only read 1 single prior record before current cursor position. Has anybody else run into this problem?

Exec SQL                                   
     Declare C1 scroll cursor for vendors; 

Exec SQL                                  
     prepare vendors from :@sqlstatement; 
                                          
Exec SQL                                  
     open C1;                             

If PageDown;
Exec SQL                                            
     fetch next from C1 for 14 rows into :@subpage; 
elseif PageUp;
Exec SQL                                                    
     fetch relative -14 from C1 for 14 rows into :@subpage; 
endif;

[jamief]

Re: Fetch relative not working

I could only get the multiple record fetches to work with occurs.....
I just process record by record.

Please let us know how this turned out

thanks
jamie

[kitvb1]

Re: Fetch relative not working

what is in your prepare statement?

[Jharriman]

Re: Fetch relative not working

Yes, the fetch next for multiple rows only works with a multiple occurrence data structure as far as I know. My Sqlstatement contains the following:

@sqlstatement = 'select incode, tonum, ' +         
                'rrn(dxctlxr) as vrrn ' +        
                'from dxctlxr' +                
                'where dsource = ''DC'' and ' +    
                      'dtype   = ''CHECK'' and ' + 
                      'direct  = ''I'' and ' +     
                      'dcode   = ''2'' and ' +     
                      'dltyp   = ''VEND'' ' +       
                      ' order by incode';

[Mercury]

Re: Fetch relative not working

BTW do you actually load 14 rows in the MODS ?

[Jharriman]

Re: Fetch relative not working

Yes, the fetch next works great.

[B.Hauser]

Re: Fetch relative not working

It depends where the cursor is positioned!

If you fetch a block of rows, after the fetch the cursor is positioned at the last row fetched. If you fetch the next block, it starts with the current cursor position and adds 1. If you fetch relative the start position is calculated from the current cursor position.

From the SQL Reference:

Multiple Row Fetch
FOR variable or integer ROWS
Evaluates variable or integer to an integral value that represents the number of rows to fetch. If a variable is specified, it must be a numeric variable with zero scale and it must not include an indicator variable. The value must be in the range of 1 to 32767. The cursor is positioned on the row specified by the orientation keyword (for example, NEXT), and that row is fetched. Then the next rows are fetched (moving forward in the table), until either the specified number of rows have been fetched or the end of the cursor is reached. After the fetch operation, the cursor is positioned on the last row fetched.

For example, FETCH PRIOR FROM C1 FOR 3 ROWS causes the previous row, the current row, and the next row to be returned, in that order. The cursor is positioned on the next row. FETCH RELATIVE -1 FROM C1 FOR 3 ROWS returns the same result. FETCH FIRST FROM C1 FOR :x ROWS returns the first x rows, and leaves the cursor positioned on row number x.

Birgitta

[Jharriman]

Re: Fetch relative not working

I saw this earlier and didn't think about it much. You're right. All I had to do was position my cursor correctly. Thanks!

@top = 1 - sqler3

If PageDown;
Exec SQL                                            
     fetch next from C1 for 14 rows into :@subpage; 
elseif PageUp;
Exec SQL      
     fetch relative :@top from C1 into :@subpage; 
Exec SQL                                              
     fetch relative -14 from C1 for 14 rows into :@subpage; 
endif;