It's doesn't need to be zz. It could be anything. qualifying the
subselect gives it a name so that the fields in it can be referenced by
that name. for example, I know several programmers who always qualify
their fields names by they table name or the name of the subselect.

Select zz.FIELDA from ( select filea.FIELDA FROM FILEA UNION ALL select
fileb.FIELDA from FILEB) zz


zz.FIELDA is select from the result of the subselect.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.



midrange-l-bounces@xxxxxxxxxxxx wrote on 03/13/2008 05:20:05 PM:

What does the zz do, and do I have to do anything with it in my
application?


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
Michael_Schutte@xxxxxxxxxxxx
Sent: Thursday, March 13, 2008 4:16 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Question - Merging files or result sets?


You need to qualify the subselect. notice the zz I put...


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.




midrange-l-bounces@xxxxxxxxxxxx wrote on 03/13/2008 05:09:49 PM:

I must have something wrong... it doesn't like it:

SELECT COUNT(*) FROM (SELECT <field list> FROM <file1> WHERE <where>
UNION ALL SELECT <field list2> FROM <file2> WHERE <where>)


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
Michael_Schutte@xxxxxxxxxxxx
Sent: Thursday, March 13, 2008 3:40 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Question - Merging files or result sets?


Take the working statement that you have. And wrap this around it.

Select Count(*) from ( ... your working statement here ... ) zz




Michael Schutte
Admin Professional
Bob Evans Farms, Inc.



midrange-l-bounces@xxxxxxxxxxxx wrote on 03/13/2008 04:35:44 PM:

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.


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

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].