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)