|
Doug I love Query/400 I use it a lot. It is true that the way it works, if you have multiple files you cannot easily do a join of one line this file info that file info other file info, instead you get one line from this match, one line from all those other matches, so that there are in fact the appearance of duplicate lines. In that circumstance, I find it useful to use intermediate work files. The last option of the main Query screen is your choice of output SCREEN REPORT or WORK FILE DETAIL or SUMMARY I use a separate library just for Query/400 work files & IBM *OUTFILES This makes it easy to tell everyone that this extra stuff is in our XFILES and on the off chance that one of my co-workers messes up & names the output as a file name we already have, they are only replacing what is in the XFILES, not one of our live production files. I have some output that is SUMMARY but it every field is either a control break of a totals. The reason for this is that I can populate a Query work file with what should be the output if there was not this doubling problem, then use that work file as the input to another query. Here is an example of how that works. We have this humongous history file with tons of records. We want to know what is the total scrap by category for a date range. The user takes a menu option which runs a CL. The CL has RUNQRY with selection *YES so that the user keys the date range. The output from that Query/400 replaces the work file with the summary data. The CL then links the work file to some other file to illuminate what each of the summarized fields means & we have a nice clean report. A lot of people want to see reports based on percentages. If you have a calculated field & you take an average of that column, you are averaging the numbers in the column, not getting a true average of the total data. But if you output the report to a work file, then use the work file as input to another query, you can sort on that calculated percentage ... perhaps you not interested in this but I sometimes have trouble selecting records on basis of calculated results when some of them are divide by zero ... Query can handle that Ok to report, but not using the data for selection, even when my criteria says to exclude that scenario, but if I send it to a work file, garbage & all, then my next query can say to exclude the garbage, and that works. So I ramble on. Your first query combines the data into a work file that has the data from the various files & summarizes it with lots of control breaks on fields that normally would be unneccessary control breaks. WORK FILE output, not printer or screen, but you use one of the others first while polishing the query. SUMMARY output, not detail so now you have a work file with all the data merged you need for the ultimate report & use it as input, sorted on basis of final version. In your example you do not have item when doing payment amount. Multi-formats & Query not a practicality. You need to have a column for each unique field, which in some cases will not be populated. I do have some multi-line queries, but find the hassle getting the columns to line up nice in registration hardly worth the trouble. The AS/400 Network has a forum with Q+A+tips for combination of Query/400, SQL, DB2, OPNQRYF & stuff like that ... you might find some useful ideas at http://www.as400network.com/Forums/Main.cfm?CFApp=59 > From: djohnson@Univbkstr.com (Doug Johnson) > > Greetings, > > I am trying to write a query that will pull data from several files. They > do have a common field that I will use for the selection, but joining the > files on that field of course results in extraneous records in the output. I > need some way to merge the data selected instead and then sort it by > customer and date. > > This is what we are looking for as output for example: > > charge account activity sorted by account # and date > > Customer number Date1 Item1 Purchase amount > Date1 Item2 Purchase amount > Date2 Item1 Purchase Amount > Date3 Payment amount > Date4 Item1 Purchase amount > Date5 Payment Amount > > > Purchases and payment history are separate files with different fields and > formats. > > Am I missing the obvious here or is it not possible to do this with query? > > Thanks, > > -doug > > > ************************************************ > DOUGLAS JOHNSON > System Administrator > The University Book Store > 711 State Street > Madison, WI 53703 > > djohnson@univbkstr.com > 608-257-3784 > 608-257-9479(fax) MacWheel99@aol.com (Alister Wm Macintyre) (Al Mac) BPCS 405 CD Manager / Programmer @ Global Wire Technologies Incorporated http://www.globalwiretechnologies.com = new name same quality wire engineering company: fax # 812-424-6838
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com 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.