Re: SQL FETCH

["David Morris" <dmorris@xxxxxxxxxxxxx>]

Phil,

When you have keys from more than one table and you specify dynamic scroll, 
a copy of your data will be made.  If the keys do not change you can get around 
this restriction by just specifying the key fields in your sql statement and 
then 
retrieving the dynamic data using the key values.  It is a pretty rare case 
where 
you can do this but if you can it is better than de-normalizing a file.

A lot depends on what you are trying to do.  Why do you need scroll?  Is 
someone 
paging through this data interactively?  Would building a work file explicitly 
(the 
system will supply one if you don't) be a better solution?  Is the selection 
criteria 
static enough to use a view (which will still build an access path in the case 
you 
describe)?  

David Morris

>>> sublime78ska@yahoo.com 11/20/00 07:38PM >>>
I pasted a piece from the Info Center.  Apparently you
can't ensure that the cursor is "sensitive" to db
updates.  SCROLL "may or may not have immediate
sensitivity" and DYNAMIC SCROLL is ignored when "The
ORDER BY or GROUP BY clauses specify columns which are
not all from the same table" which is the case in my
scenario.

Is this everyone else's experience or am I missing
something?

Thanks, 
Phil

from
http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/index.htm 
:

SCROLL 
Specifies that the cursor is scrollable. The cursor
may or may not have immediate sensitivity to inserts,
updates, and deletes done by other activation groups.
If DYNAMIC is not specified, the cursor is read-only.
In addition, the SELECT statement cannot contain a FOR
UPDATE clause. 

DYNAMIC SCROLL 
Specifies that the cursor is updateable if the result
table is updateable, and that the cursor will usually
have immediate sensitivity to inserts, updates, and
deletes done by other application processes. However,
in the following cases, the keyword DYNAMIC is ignored
and the cursor will not have immediate sensitivity to
the inserts, updates, and deletes: 
Queries that are implemented as temporary result
tables. A temporary result table is created when: 

The ORDER BY or GROUP BY clauses specify columns which
are not all from the same table



+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---