RE: SQL Question - Merging files or result sets?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Alan Shore
Sent: Thursday, March 13, 2008 3:43 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Question - Merging files or result sets?



Brad - could you re-post your question about the count as I don't seem to
have received that.



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill




"Bradley V.

Stone"

<bvstone@bvstools
To
.com> "Midrange Systems
Technical
Sent by: Discussion"

midrange-l-bounce <midrange-l@xxxxxxxxxxxx>

s@xxxxxxxxxxxx
cc



Subject
03/13/2008 04:37 RE: SQL Question - Merging
files or
PM result sets?





Please respond to

Midrange Systems

Technical

Discussion

<midrange-l@midra

nge.com>









Well, I got everything working slick now except the count. :) Still
hoping
someone has ideas on that.

Thanks to everyone for your help so far!

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 Wilt, Charles
Sent: Thursday, March 13, 2008 3:31 PM
To: Midrange Systems Technical Discussion
Subject: 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.
--
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.