Re: Define a Join over two files with same field names

Using * requires that the field names and types are equal in both files.

Since they are different, you will need to specify the names from the original files and "AS" with the name for the resulting file.

On 9/17/19 12:04 PM, Art Tostaine, Jr. wrote:

Thank you Raul. That's still not working though.

CREATE View tstord/orderhead43
AS SELECT * from tstord/wkvcohead as U
UNION ALL
SELECT * from tstord/vcohead as P

Message ID . . . . . . : SQL0153 Severity . . . . . . . : 30

Message type . . . . . : Diagnostic



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, EXCEPT, or

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




On Tue, Sep 17, 2019 at 11:59 AM Raul Jager <raul@xxxxxxxxxx> wrote:

CREATE View r37files/orderhead43

AS SELECT * from wkvcohead

UNION ALL

SELECT * from vcohead

On 9/17/19 11:27 AM, Art Tostaine, Jr. wrote:

I have two order header files that are identical. One is posted orders

and

one is unposted.

I want to join them so I can read both. I need to see the invoice#

(OAINV)

from both files.

I tried this but received error that column list required. Is this
possible? Thank you

CREATE VIEW r37files/ordhed43
AS SELECT u.oaord, p.oaord, u.oarev#, p.oarev#
FROM wkvcohead u, vcohead p
WHERE u.oacmp = p.oacmp
and u.oaord = p.oard

-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
-- Verificado por Anti-Virus Corporativo Symantec --
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

-- Este e-mail fue enviado desde el Mail Server del diario ABC Color -- -- Verificado por Anti-Virus Corporativo Symantec --