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

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