RE: SQL Question - Merging files or result sets?

Personally, I don't like using prepared statements... So I wonder if this
would work for you Bradley?

DECLARE GLOBAL TEMPORARY TABLE TEMPTAB1 LIKE FILEA

If Search = 'A'
INSERT INTO TEMPTAB1 SELECT * FROM FILEA
EndIf
If Search = 'B'
INSERT INTO TEMPTAB1 SELECT * FROM FILEB
ENDIF

Declare C1 Cursor For
Select * from TEMPTAB1

Fetch C1 into :whatever
Dow sqlcod >=0 and sqlcod <> 100;
fetch c1 into :whatever
Enddo



Michael Schutte
Admin Professional
Bob Evans Farms, Inc.

midrange-l-bounces@xxxxxxxxxxxx wrote on 03/13/2008 03:38:17 PM:

Would

ORDER by x, y, z

work? Where x, y and z are numbers representing the position of the

fields

in the result set that I want to sort by?

I just found an example online that did that. Can I use numbers for

SELECT

clause too? That would mean I'd only need one select clause, which would

be

great.

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.

--
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.