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

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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
> >
> >
> > _______________________________________________
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.