Re: SQL Cursor Questions

[Boris Goldenberg <bpcsguru@xxxxxxxxx>]

Not sure why interactively SQL is using the access path but the cursor does
not.  To answer your other question, go ahead and use the physical file for
your cursor and use WHERE and ORDER BY clauses.  Example:

DECLARE C CURSOR FOR SELECT * FROM ECL
              WHERE LID = 'CL' AND (LPROD BETWEEN 
              :W1FITM AND :W1TITM) ORDER BY LPROD 
              FOR FETCH ONLY
OPEN C
FETCH C INTO :W7ECL

DOW         (*SQLSTS = 0)
.....
FETCH C INTO :W7ECL
ENDDO
CLOSE C

Regards,
Boris Goldenberg

--- "Nelson C. Smith" <ncsmith@gate.net> wrote:
> I have a couple of questions regarding SQL Cursors.
> 
> First, I'm dynamically building a select statement to fetch multiple records
> from a keyed logical file without an Order by clause.  I'm using the
> Prepare, Declare, Open, Fetch, Close method.  The cursor is positioned
> correctly and returns the correct number of records.  However, even though
> I'm selecting records from a keyed logical file, the records are ordered by
> the physical file's RRN order.  In other words, the access path associated
> with the logical file is not used.  When I execute the very same statement
> interactively, it uses the access path of the logical.  The only difference
> is the cursor is not used in the interactive version.  Is this normal
> operation for cursors or am I doing something wrong? I know I can use an
> Order by clause to put the records in the same order as the logical is
> keyed, but why doesn't it use the logical's access path in the first place?
> 
> Second, if I use a where clause to set the location of my cursor, I am also,
> in effect, subsetting the list to that same where clause and all my fetches
> are limited to that subset.  What I really want to do is set the cursor to a
> particular record, but then be able to fetch records without regard to any
> where clause.  In other words, I want to do a SETLL with a key, and then
> READ or READP without a key.  Is there any way to do this? (besides using
> RPG, of course ;-)
> 
> 
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: david@midrange.com
> +---
> 

__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---