Re: Proper way to do some file operations with SQL

The proper SQL way? ;)

1) Use a auto-generated identity column or maybe a sequence.

2) Remove the summary columns from the header. Build a view over the
header with the summary columns calculated. Perhaps with some EVI indexes
over the detail so that the summary values are pre-calculated by the DB.

Alternate 2) Use a trigger on the detail to maintain the summary columns in
the header (or perhaps a MQT)


SQL RLA doesn't offer any advantages over RPG RLA...I'd go so far as to say
that SQL RLA is at a disadvantage compared to RPG RLA.

You end up with SQL RLA when you try to replace just RPG RLA op-codes with
an SQL equivalent.

Set based SQL really requires a re-write.

Granted, in the case of a CRUD application...you really can't get away from
RLA.

Charles



On Tue, Apr 17, 2018 at 9:24 AM, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>
wrote:

All,

I'm doing a small project where, for the first time, I'm trying to do /ALL/
the file ops via SQL. I have a couple of questions on the 'proper' way to
do this in SQL.

1) I'm generating a credit memo and need to use the next available credit
memo number. With RLA, one CHAINs to the control file, adds 1 to the last
credit memo number used, updates the control record, then uses the number
you just got. No one else can get this control record until you release it
so there are no duplicates. What's the proper way in SQL?

2) There's a header record and x number of detail records. The norm in RLA
is to write the header, write the detail records, then CHAIN back to the
header and update it with summary information from the detail records. I
assume the same sequence with SQL. Is there a slick way of doing this?

Thanks.


--


Jeff Crosby
VP Information Systems
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
direct.dilgardfoods.com

The opinions expressed are my own and not necessarily the opinion of my
company. Unless I say so.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD