Re: Is it possible to do this with key fields?

[Dan <dbcemid@xxxxxxxxx>]

Vern, the application in question is strictly an inquiry subfile (list of 
vendors), so this is
input-only.  The problem I am dealing with is that the program was poorly 
written and I am making
what would normally be a simple modification.  In the course of my modification 
and testing, I
found a defect with the original program that affects the page up function (in 
some cases, the
first record shown on the subfile also appears as the last record on the 
subfile after a page up).
 The modification is basically intended to add more record selection criteria 
to what shows up in
the subfile.

What I have done in the interim is to set up an OPNQRYF on the file with 
KEYFLD(*FILE)
ALWCPYDTA(*NO) OPTIMIZE(*FIRSTIO) and the new data selection.  There is a 
minimal startup delay
that I *think* the users can live with, and it solves the problem with the page 
up issue, but
introduces a new one; if page up is done from the beginning of the file, it 
rolls back to the end
of the file.  This is curious because I can't duplicate it in the original 
program or in my
modified program without the OPNQRYF.

I think that, because of the minor impact of the new problem, the SQL solution 
you propose may
become strictly academic now.  However, I would still be interested in 
investigating this for
future purposes.  Does SQL have an option like OPNQRYF's OPTIMIZE(*FIRSTIO) to 
open up the file
quickly?

TIA, Dan

--- Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> Dsn, I think there is a DYNAMIC SCROLL keyword on the DECLARE CURSOR that 
> allows positioning on the FETCH - like FETCH FIRST, etc.
> 
> You can't use a view for normal RPG IO. You CAN CPYF a view to a physical 
> file. Then create an SQL index on it. The index CAN be used in RPG.
> 
> But you don't need SETLL, etc. Just use the SCROLL keyword and you can 
> handle things just like good old RPG but with FETCH.
> 
> With DYNAMIC the cursor is updatable. Without, it is read-only.
> 
> Keyed processing can be accomplished with a host variable in the WHERE of a 
> cursor, a FOR UPDATE clause in that SELECT, a FETCH, and an UPDATE WHERE 
> CURRENT OF. But native IO is probably more appropriate for this.
> 
> Are you updating every record, or just some? Is it based on selection 
> criteria? Then maybe an update with various CASE constructs and WHERE could 
> do the job. A lot of "procedural" stuff can be accomplished with CASE.
> 
> HTH
> 
> Vern
> 
> At 06:41 AM 6/13/2003 -0700, you wrote:
> >Thanks, Vern!  I am not surprised that SQL can do this.  However, can it 
> >be used such that you can
> >SETLL and READE/READPE on that view?  The only SQL I've used in RPG 
> >programs is through a straight
> >read loop: Declare, then Open, then Fetch in a loop, then Close.  Is it 
> >possible to reposition the
> >cursor on an SQL view?
> >
> >TIA, Dan
> >
> >--- Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> > > The usual recommendation is a separate column with the sort value. Your 
> > SQL
> > > could use a CASE WHEN USER03 = '    ' THEN '0030' ELSE USER03 END -- this
> > > way you don't need to add any columns to the original table. Then sort on
> > > the derived columns.
> > >
> > > SELECT USER03, VENDOR, CASE...END SORTER FROM YOURFILE ORDER BY SORTER
> > >
> > > Vern
> > >
> > > At 11:58 AM 6/12/2003 -0700, you wrote:
> > > >Have an unusual situation with a 15-character alpha field (USER03) that I
> > > >need to turn into a key
> > > >field in a Vendor file in a new logical file.  It will hold the value 
> > of a
> > > >4-digit company number
> > > >*or* it will be blank.  The keys in the new logical will be USER03, then
> > > >VENDOR#.  Somehow, for
> > > >the purposes of reading this logical in key sequence, I need treat a 
> > value
> > > >of blanks and a value
> > > >of '0030' the same, in essence, reading the file in "sequence" as 
> > > >follows:
> > > >
> > > >USER03  VENDOR
> > > >'0020'  9877412
> > > >'0030'  1254478
> > > >'    '  2214778
> > > >'0030'  4174411
> > > >'0040'  1234567
> > > >
> > > >I think I already know that this can't be done, but is there a way to 
> > play
> > > >with the collating
> > > >sequence or somesuch?
> > > >
> > > >TIA, Dan


__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com