Re: sql help-select sum of 2 fields in flat file

["Jim Franz" <franz400@xxxxxxxxxxxx>]

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.
thanks
jim

----- Original Message -----
From: <MacWheel99@aol.com>
To: <midrange-l@midrange.com>
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
external
> 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
defined
> as the total of AMT + PAID, or I would read them as separate fields & do
the
> 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
400
> & 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,
you
> 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
mine
> to a data structure for simplicity of field definition, especially when
doing
> 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
doing
> 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
program
> 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
the
> 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
further
> contextual later on ... how do we do this, what is the command for that &
so
> forth
>
> So you might try the same path as I have just described & see what cool
stuff
> 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,
>
> MacWheel99@aol.com (Alister Wm Macintyre) (Al Mac)
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>