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: <email@example.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-Lfirstname.lastname@example.org > Before posting, please take a moment to review the archives > at http://archive.midrange.com/midrange-l. > >
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.