RE: SQL Question - Merging files or result sets?

Adam Glauser had it right a few emails ago.
Select ...where
union all
select ...where
order by ...

"Bradley V. Stone" <bvstone@xxxxxxxxxxxx> 03/13/08 3:35 PM >>>

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.