Re: SQL Question - Merging files or result sets?

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.