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)
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.