Re: query question

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