× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. March 2008

RE: SQL Question - Merging files or result sets?

Ok, it's working find when there's no union, but when there is, I get the
following error:

Message ID . . . . . . : SQL0153 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 03/13/08 Time sent . . . . . . : 16:20:55

Message . . . . : Column list required.
Cause . . . . . : A column list must be specified because the result
columns
are unnamed. Result columns are unnamed for one of the following reasons:
-- An element of the SELECT list is not a column and the AS clause is
not
specified.
-- Corresponding columns of the subselects in a UNION do not have the
same
name.
-- Two result columns have the same column name. Every column name and
system-column name must be unique in a table or view definition. If two
column names are the same, the column name is CVROLL.
Recovery . . . : Do one of the following and try the request again:
-- Provide a list of names for the columns in the table, view, or
derived
table.
-- Specify an AS clause to assign a unique name to the unnamed elements
or
to rename the duplicate columns in the SELECT list.

Statement as it is now:
SELECT COUNT(*) FROM (SELECT <field list> FROM <file1> WHERE <where>
UNION ALL SELECT <field list2> FROM <file2> WHERE <where>) qq


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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 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].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.