RE: SQL Question - Merging files or result sets? -- MIDRANGE-L

Without seeing your whole statement it's hard to determine what's actually
going on. I'm certain that I have gotten this to work before. In any
case maybe you should think about using a global temp table.

DECLARE GLOBAL TEMPORARY TABLE TEMPTABLE LIKE FILEA

IF Search = 'FILEA' or Search = 'BOTH'
INSERT INTO TEMPTABLE SELECT <field list> from FILEA
ENDIF

IF Search = 'FILEB' or Search = 'BOTH'
INSERT INTO TEMPTABLE SELECT <field list> from FILEB
ENDIF

Now you have a table that you can do whatever you want with.

SELECT <field list> from TEMPTABLE

or

SELECT COUNT(*) from TEMPTABLE

Michael Schutte
Admin Professional
Bob Evans Farms, Inc.

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

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

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.