this sql is in a rpg inquiry program over ar invoice file. The problem is
performance. Over a million record file, so I can't read all records,
summing the 2 fields in each record. Have been doing the rpgsql for about 5
years, just can't figure this complex a statement yet.
Loyd gave me something to try monday (see his prev post)
Am replacing a current rpg screen that does read all million records, in
5-15 minutes, killing system performance. Sql looks like a good fit here if
I can get this problem solved.

----- Original Message -----
From: <>
To: <>
Sent: Saturday, January 12, 2002 11:03 PM
Subject: Re: sql help-select sum of 2 fields in flat file

> I hope some of the following has been helpful to you.
> As usual, I am at home PC telling you how I would do this from memory.
> a) I do not know how to access a flat file in SQL, so I would create an
> external logical layout of the flat file, then use SQL to access the
> logical.
> b) I am much more comfortable working in RPG than SQL so I would define a
> CURSOR in SQL that is to process the flat file in the appropriate records
> sequence, knowing that each time I run the SQL read a record it would take
> one record in the sequence specified.  My input would be a SUM file
> as the total of AMT + PAID, or I would read them as separate fields & do
> addition in RPG.
> You can also get at a TOTAL of all of the AMT of all records in the file &
> ditto for all PAID so that your SQL access gets you the grand total in one
> read without needing the cursor route.  I not remember the precise syntax
> because like I said I do all my e-mail from home PC that has no access to
> & I do all my 400 work without internet access.  2 worlds apart.
> If you are not familiar with SQL CURSORs it is like defining a compound
> record that could come from many files ... each time you read the cursor,
> are reading a join of selected files & just the fields from those files
> specified for the cursor.
> There are several places in the program to take care of.
> Define the cursor ... relationship, what you reading ... I usually input
> to a data structure for simplicity of field definition, especially when
> later modifications
> Open the cursor (like opening a file)
> Actually read the next "record cluster" ... like READ or CHAIN in RPG only
> simpler
> When all done, close the cursor.
> I am constantly discovering new cool stuff you can do with the AS/400.
> Some of it I discover here & other forums & lists.
> Some of it I discover by pure good fortune on the 400 at work.
> I am totally opposed to experimentation where there is any risk of me
> any damage to files, but some experimentation is relatively benign safe.
> A couple of nites ago I had occasion to do an SQLRPG program in which I
> wanted to access a file that would not be in the library list of the
> at the time of its execution & I was unsure of the format for accessing a
> file with qualified library from within SQLRPG & forget about me having
> access to a manual any time soon, so on a hunch I did an F4 from within
> SQL line of code in my RPG program, that is after EXEC SQL and before END
> EXEC, to see how much support might be there.
> Cool, that got me what I wanted LIB/FILE
> and I also saw that it got me into a world of SQL F1 type stuff for
> contextual later on ... how do we do this, what is the command for that &
> forth
> So you might try the same path as I have just described & see what cool
> you can stumble over where I have perhaps blazed a trail that the experts
> here knew so well that it did not occur to us to ask them if this existed,
> (Alister Wm Macintyre) (Al Mac)
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> To post a message email:
> To subscribe, unsubscribe, or change list options,
> visit:
> or email:
> Before posting, please take a moment to review the archives
> at

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.