RE: SQL Position-To with scroll-backwards capability

["Tyler, Matt" <mattt@xxxxxxxxxxxxxx>]

SQL cursors are row number oriented, meaning there is no SETLL/READE within
the row set.  If the user needs to "position to" then scroll the data by
fetching and inspecting the result set.  Once you found that record that is
closest, you then FETCH RELATIVE :ROW FROM SQLCSR (where row would be ROW-1)
and then read from there.  

One thing I do is to have a somewhat position to field, that creates a
condition in the SQL of FIELD>=value and only allow roll down from there.
To go before the position to data, a new value must be given or cleared out.

I suppose you could create the SQL as *QMQRY object, pass parameters, run
STRQMQRY to a QTEMP work file (static result set, however), open that file
in your RPG and perform IO like any native file (assuming you created a
template file for the *QMQRY to put results into with keys).   I have seen
this approach take longer to process since the entire result set must be
created before you can work with it. 

Can you use the last name in the SQL with similar LIKE features?  This might
narrow down the number records to view.  First name is quite vague for
searching.  If they do not know the last name (or a close match) how can
they expect to find anyone?

<SNIP>
The requirement for dynamic wildcard searches indicates the need for using
SQL.
</SNIP>
        Don't fall into this trap.  The only reasons I can think of that you
should always consider SQL would be ordering data different than the access
path that you retrieved the data from (or by another file) or aggregate
functionality (needing the GROUP BY clause like SUM(), COUNT(), etc.) that
span multiple files.   
        If you do the selection yourself, you just have to have more files
defined and do more work in your program.  I have several programs that use
three or four or five different indexes to access the data based on the
combination of the user request.  It gets more complicated the more
selections users are given or ask for.  If they need sorting not based on
the indexes used for IO then SQL makes perfect sense.  


Thank you,
Matt Tyler
WinCo Foods, LLC
mattt@xxxxxxxxxxxxxx