Unions in OW

markjde

Active Member
I have a table that contains current order header records and another table
that contains order header history records. What I am attempting to do is
create a business view that is essentially a union of both tables.

Then within a find/browse form use this business view to access the order
header records regardless of if they are current or historical records.

This is similar to V42119B which combines both current and historical sales
tables into a single business view.

The problem is that I am not able to select records from the second table to
create the business view. I have check out the V42119B and I can't even
select fields from the second table to modify this business view (not that I
want to do this, but just as a test).

Any ideas how I can create a similar business view to V42119B so that I can
create one myself for my application.

Thanks.

Mark

[email protected]
B7332 SP11, Intel NT4, SQL 7.0

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
 

Larry_Jones

Legendary Poster
Mark,

no problem. If you've defined the join as a Union then any fields you've selected from the first table will by definition be selected from the second table also. Remember - to do a Union tables have to be identical in layout.

Regards,

Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
 

JOHNA

Member
I created a Business View with three tables, and used a simple table JOIN for the keys in each table being the same. And I was able to retrieve columns (fields) from all three tables within my Business view for a Find/Browse form.

>>> markjde <[email protected]> 01/11/01 12:10AM >>>
I have a table that contains current order header records and another table
that contains order header history records. What I am attempting to do is
create a business view that is essentially a union of both tables.

Then within a find/browse form use this business view to access the order
header records regardless of if they are current or historical records.

This is similar to V42119B which combines both current and historical sales
tables into a single business view.

The problem is that I am not able to select records from the second table to
create the business view. I have check out the V42119B and I can't even
select fields from the second table to modify this business view (not that I
want to do this, but just as a test).

Any ideas how I can create a similar business view to V42119B so that I can
create one myself for my application.

Thanks.

Mark

[email protected]
B7332 SP11, Intel NT4, SQL 7.0

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.




--------------------------
To view this thread, visit the JDEList forum at:
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=0&Board=OWDEV&Number=3723
*************************************************************
This is the JDEList One World / XE Developers Mailing List.
Archives and information on how to SUBSCRIBE, and
UNSUBSCRIBE can be found at http://www.JDELIST.com
*************************************************************
 

Adrian_Chimirel

Legendary Poster
John,
It is my understanding (and I'd be happy if you let me know where I am wrong) that Mark wants a UNION (that is being able to get ANY record from ANY table, no restriction on any key), while you are suggesting a JOIN (that will show only those records from the tables, where the key is the same).

Larry,
I got into Demo-Xe and:
1-I tried to select a field from the second table (SO History) and ... as Mark was saying, nothing happens
2-I tried to select a field from the first table (SO Details) and only that field (alone) gets on the View's Selected Columns list
(even after saving and generating the view).

I'm sorry Mark, but it seems there is a bug; I'd open a call with RL. Please post your solution afterwards (otherwise I'll open that call myself).

Thank you all,
Adrian

LIVE: B732.1 SP12.2, Oracle 805
SANDBOX: B733.3 SP3, Or. 8i
RS/6000, Citrix, 200+ clients
 
Discover how to build no-code data integrations and business process automations.

owguru

Well Known Member
Mark,

Larry is right on the money here. I was a little freaked out when I tried my first union also. Basically, you use two identical tables here, the current and the historical. Their columns are the same.

If you are having trouble selecting columns on the first table (as I have had in the past), then delete the second table, removing the union. This will allow you to select all the columns you need in table 1. Then add table 2 and specify a union. The bsvw know automatically to retrieve the same columns from table 2 as table 1. You don't need to check them in table 2 (and as you have seen, you can't).

If you're still unsure, create this bsvw and a quick report with a few of the columns...then run to see if you get records from both tables.

owguru (at least I'm trying :)
>all versions
>all platforms
 

JOHNA

Member
Adrian,
I thought (Mark) his tables had keys alike which if they did the join would work.
>>> Adrian_Chimirel <[email protected]> 01/11/01 11:24AM >>>
John,
It is my understanding (and I'd be happy if you let me know where I am wrong) that Mark wants a UNION (that is being able to get ANY record from ANY table, no restriction on any key), while you are suggesting a JOIN (that will show only those records from the tables, where the key is the same).

Larry,
I got into Demo-Xe and:
1-I tried to select a field from the second table (SO History) and ... as Mark was saying, nothing happens
2-I tried to select a field from the first table (SO Details) and only that field (alone) gets on the View's Selected Columns list
(even after saving and generating the view).

I'm sorry Mark, but it seems there is a bug; I'd open a call with RL. Please post your solution afterwards (otherwise I'll open that call myself).

Thank you all,
Adrian

LIVE: B732.1 SP12.2, Oracle 805
SANDBOX: B733.3 SP3, Or. 8i
RS/6000, Citrix, 200+ clients
--------------------------
Visit the forum to view this thread at:
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Number=3764
*************************************************************
This is the JDEList One World / XE Developers Mailing List.
Archives and information on how to SUBSCRIBE, and
UNSUBSCRIBE can be found at http://www.JDELIST.com
*************************************************************
 

Adrian_Chimirel

Legendary Poster
Dear List,
Could anybody, please, tell us (John & I) what are the pros and cons in using UNION against JOIN?
Thank you very much,
Adrian

LIVE: B732.1 SP12.2, Oracle 805
SANDBOX: XE SP3, 8i
RS/6000, Citrix, 200+ clients
 

Larry_Jones

Legendary Poster
Adrian,

A "normal" join that you refer to is techincally a "equi-join" where you are joing two or more tables based on comparisons between one or more columns.

A UNION join is basically a merge of two (or more) queries / tables whose column lists (number of columns and data types) are identical (Column lengths may differ however).

A union differs from a equi-join in that an equi-join looks to join rows from two or more tables based on the presence of matching columns (the join criteria) in each table. The Union has one set of columns whose data may have come from either table. Attempting to use an equi-join to imitate a union would require two sets of columns - one for eacg source table/query.

You could not use an Equi-Join for example to imitate the results of a UNION join of the Sales Order detail and detail history tables. The results would two Order Number, qty, etc fields - one for each source table.

OK, I've obfuscated things enough - somebody else give it a try :)


Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
 

Zoltan_Gyimesi

Legendary Poster
Larry, you wasn't obfuscated but clear enough.
Adrian, again:
The keyword is MERGE. Union is something like when you have three tables with identical structure and have data in 1 and 2 and you copy all rows from 1 and 2 into 3. Now, 3 is the UNION.
Hope could help a bit,
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

tmesser

Member
Looks good to me Larry. I think you did a good job of explaining it.

By the way, are you familiar with any other files (aside from the F4211 and
F42119) where the files are identical for a union join?

Tom
----- Original Message -----
From: "Larry_Jones" <[email protected]>
To: <[email protected]>
Sent: Friday, January 12, 2001 2:39 PM
Subject: Re: Unions in OW ~~3723:3915


> Adrian,
>
> A "normal" join that you refer to is techincally a "equi-join" where you
are joing two or more tables based on comparisons between one or more
columns.
>
> A UNION join is basically a merge of two (or more) queries / tables whose
column lists (number of columns and data types) are identical (Column
lengths may differ however).
>
> A union differs from a equi-join in that an equi-join looks to join rows
from two or more tables based on the presence of matching columns (the join
criteria) in each table. The Union has one set of columns whose data may
have come from either table. Attempting to use an equi-join to imitate a
union would require two sets of columns - one for eacg source table/query.
>
> You could not use an Equi-Join for example to imitate the results of a
UNION join of the Sales Order detail and detail history tables. The results
would two Order Number, qty, etc fields - one for each source table.
>
> OK, I've obfuscated things enough - somebody else give it a try :)
>
>
> Larry Jones
> [email protected]
> OneWorld B733.1, SP 11.3
> HPUX 11, Oracle SE 8.1.6
> --------------------------
> Visit the forum to view this thread at:
>
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Numbe
r=3915
> *************************************************************
> This is the JDEList One World / XE Developers Mailing List.
> Archives and information on how to SUBSCRIBE, and
> UNSUBSCRIBE can be found at http://www.JDELIST.com
> *************************************************************
>
>
 

BBritain

VIP Member
I'll give it a try....

Start with one table....

When you join a table you are adding columns of information; when you union
a table you are adding records.....is that concise enough?

Ben again
 

Larry_Jones

Legendary Poster
Thanks Zoltan and Tom.

Regarding your question Tom, I am not familiar with other OneWorld views that are based on joins (but I haven't looked either).

FYI in the database world (not OneWorld) the tables do not have to be identical - just the list of fields selected (same datatypes and number of fields).

P.S. Zoltan - saw your post on work you did with translations ... very impressive - makes us all the more appreciative of your excellent suggestions offered so humbly (yes - this is a compliment!).

Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
 

owguru

Well Known Member
...one more note. Check out the ow developer guide on business view development. In b7332 version, and I believe this is a holdover from several releases back, there are some great diagrams to illustrate unions, joins, left and right outer joins...the works.

owgura (at least I'm trying :)
>all versions
>all platforms
 

Adrian_Chimirel

Legendary Poster
There is something funny: I replied my "Eureka /I thing I got it!" on Friday evening and ... it didn't get posted on the Forum! I don't know if the List got my reply, please can somebody confirm? In other words, do I have to re-post my reply? Thx, Ady

LIVE: B732.1 SP12.2, Oracle 805
SANDBOX: XE SP3, 8i
RS/6000, Citrix, 200+ clients
 

Adrian_Chimirel

Legendary Poster
Re: RE: Unions in OW

Eureka!
I guess I can better answer John, now: Using a JOIN, you cannot retrieve all history records (especially those where you modified one of the fields that make up the key) and this is where UNION comes in really handy.
Thank you everybody, it was nice storming my brain with you/your valuable knowledge; can't get enough of it, can I?
Ady

LIVE: B732.1 SP12.2, Oracle 805
SANDBOX: XE SP3, 8i
RS/6000, Citrix, 200+ clients
 
Top