Re: SQL Question - Merging files or result sets?

[Jeff Young <cooljeff913@xxxxxxxxx>]

Brad,
Try this:
Select 'A' sort, select a.* from fileA a where ....
UNION ALL
select 'B' sort, b.* from fileB b where ....
 order by sort,.....



Jeff Young 
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3 


  
 





----- Original Message ----
From: Bradley V. Stone <bvstone@xxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, March 13, 2008 3:35:46 PM
Subject: RE: SQL Question - Merging files or result sets?

Yes, that looks like exactly what I want, except the ORDER by clause.

Basically what I want to do is:

select * from fileA where ....
UNION ALL
select * from fileB where ....

Now, how do I order the unioned result set?  If I use order by using fields
from fileA, will it mix in the fileB records as well?

Bradley V. Stone
BVSTools - www.bvstools.com
eRPG SDK - www.erpgsdk.com

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Adam Glauser
> Sent: Thursday, March 13, 2008 1:32 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Re: SQL Question - Merging files or result sets?
>
>
> Bradley V. Stone wrote:
> > This isn't a join, it's two files with identical layouts, but different
> > field names and different category of data.  Otherwise the files are
> > identical.
> >
> > When a user does a search, they can search file A, file B, or
> both.  So I'd
> > like to try to write this dynamic enough so all I do is check the search
> > flags they set and build my SQL appropriatly.  Especially when
> they search
> > both so it will "mesh" the data from the two files in the result set.
>
> I think that UNION will do the trick.  Perhaps you could do
> something like:
>
> selectA = 'SELECT <the fields> FROM fileA WHERE <criteria>'
> selectB = 'SELECT <the fields> FROM fileB WHERE <criteria>'
> order  = ' ORDER BY <ordering fields>'
>
> if (search = both A and B)
>    query = selectA + ' UNION ' + selectB + order
>
> elseif (search = A only)
>    query = selectA + order
>
> else
>    query = selectB + order
>
> Would something like that do what you're after?
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.