RE: SQL Question - Merging files or result sets?

Sure...

select 'FILEA' as orgFile, a.* from fileA a
union all
select 'FILEB' as orgFile, b.* from fileB b

Now the first field tells you the originating file.

Charles Wilt
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Bradley V. Stone
Sent: Thursday, March 13, 2008 4:26 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Question - Merging files or result sets?

Ha... one more question I'm guessing the answer is "no".. but is there
any
way to know which file the record from the result set came from if it was
a
union? :)

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
lgoodbar@xxxxxxxxxxxxxx
Sent: Thursday, March 13, 2008 3:11 PM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: SQL Question - Merging files or result sets?


If you name the fields, you can substitute a literal for the missing
field.

Select field1a as firstfield, '' as secondfield from file1
Union all
Select field2a, field2b from file2
Order by 1

Loyd Goodbar
Senior programmer/analyst
BorgWarner
TS Water Valley
662-473-5713

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Bradley V. Stone
Sent: Thursday, March 13, 2008 15:08
To: Midrange Systems Technical Discussion
Subject: RE: SQL Question - Merging files or result sets?

Ok, Darn. Almost worked. I found out the file in production (fileB)
does
have one extra field at the end.

Message ID . . . . . . : SQL0421 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 03/13/08 Time sent . . . . . . :
15:06:05

Message . . . . : Number of UNION operands not equal.
Cause . . . . . : The operands of a UNION or UNION ALL must have the
same
number of columns.
Recovery . . . : Correct the SQL statement so that it has the same
number
of operands in each SELECT list.

Is this telling me it won't work because the files don't have the same
number of coumns?

For select I'm using select * and fetching into a DS defined externally
by
fileA.

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


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

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.