Re: Merging the results of three queries

["Benzenhafer, Dave" <DBenzenhafer@HOUSTON.DEEPWATER.COM>]

Thanks Ray for you input. I think I will try the array. It will probably
only be a couple of hundred lines long and 4 fields. As I understand it you
can also increase the size of the array as you go if need be.
This sounds easier than a union.

Also Thanks to Amy for her suggestion.


Dave Benzenhafer
EMPAC Custom Reports
(601) 792-4857  (601) 714-6970
dbenzenhafer@houston.deepwater.com


-----Original Message-----
From: Ray Ontko [mailto:rayo@ONTKO.COM]
Sent: Friday, April 26, 2002 11:49 AM
To: SQR-USERS@list.iex.net
Subject: Re: Merging the results of three queries


Dave,

One approach would be to do a union on the three queries
and order by date and source, where source is A, B, or C depending
on which query the datat came from. Then, break on date.  Each
time the date changes, you go to the next line of output before
printing the data.

Another approach: If the number of rows isn't too big, this could
be a job for an array.  Create an array that has one field for the
date, and three fields (or occurrences) for the quantity.

As you process the results of each query, look for the date in
the array.  If it's not there, insert a new row with the new date.
Set the corresponding quantity.

Then, when you're done, you should have an array with all the
information merged.  Just loop through the rows of the array
and print the information.

Ray

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> I am using SQR 4.3.4
> I have three queries that output a date and a quantity.
>
> Query 1 Output                Query 2 Output                    Query 3
> Output
> Date                Qty            Date                    Qty
> Date                        Qty
> 01-OCT-01        2            01-OCT-01             3
02-OCT-01
> 6
> 02-FEB-02         3            05-JAN-02             2
> 12-DEC-01                 8
> 03-FEB-02         5            03-FEB-02              5
> 03-FEB-02                 9
>
> I would like to merge the three results in to something that looks like
> this.
>
> Date            Output 1            Output 2            Output 3
> 01-OCT-01        2                        3                        0
> 02-OCT-01        0                        0                        6
> 12-DEC-01        0                        0                        8
> 05-JAN-02        0                        2                        0
> 02-FEB-02        3                        0                        0
> 03-FEB-02        5                        5                        9
>
> I don't want to have to query each day of the year.
> Any suggestions will be appreciated.
>
>
> Dave Benzenhafer
> EMPAC Custom Reports
> (601) 792-4857  (601) 714-6970
> dbenzenhafer@houston.deepwater.com
>

----------------------------------------------------------------------
Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/