Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Many-to-one JOIN - is this valid?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Many-to-one JOIN - is this valid?
 Login/Join
 
Member
posted
I recently found out that a colleague had a mainframe FOCUS program with a many-to-one JOIN that she'd been using for several years. From reading my 7.0 FOCUS manual, I'd always thought that only one-to-one and one-to-many JOINS were valid (in other words, each record in the host file had to be unique based on the JOIN fields used). Unless I misunderstood the text, the manual doesn't state that a many-to-one JOIN won't work but it only mentions using the one-to-one and one-to-many joins. I tested out a many-to-one JOIN on mainframe FOCUS 7.1.1 using a DB2 table as the host file and an indexed FOCUS format hold file as the target file, thinking that it probably wouldn't work correctly but it seemed to work just fine. I'm a little wary of using one, though, as I can't find it documented as a valid JOIN.

I did find a post where a forum member that noted the way a JOIN works when the host and targets have different formats is that it reads in a host record and finds associated records in the target file before moving on to the next host record and finding the next record's associated target file records. If this is correct, then I could see how a many-to-one JOIN just might work. Does anyone know if a many-to-one JOIN can be used with reliable results? Does anyone know of any documentation that says it should not be used?


Chris

This message has been edited. Last edited by: Kerry,


WebFOCUS 7.6.8; Windows
FOCUS 7.1.1; z/OS 1.09.00
 
Posts: 3 | Registered: July 17, 2009Report This Post
Guru
posted Hide Post
Maybe someone has stronge technical background can explain how joins are architectured.

Logically, this type of join is commonly used to join transactions files to master files. And the results are very reliable.


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Master
posted Hide Post
Many to one doesn't work well for me most of the times. If there is that kind of join I prefer MATCH. I guess Masters would provide detaield info..thank u..


8.1.05
HTML,PDF,EXL2K, Active, All
 
Posts: 484 | Registered: February 03, 2009Report This Post
Virtuoso
posted Hide Post
There is no intrinsic reason why a many-to-one join shouldn't work. I have used them often, when appropriate or necessary. The best way to ensure reliable results, as you discovered, is to make certain the target file is indexed on the join-to field/column.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Platinum Member
posted Hide Post
A many to oner join IS a i=one to one join. There just happens to be duplicate values in the FROM file that match the same value in the TO file.

There is nothing special about many-to-one.
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
Expert
posted Hide Post
Ditto with OPALTOSH, but as Dan says, the target join column MUST be indexed, or in an RDBMS.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Or the two files/table must be sored in he same order.
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
Member
posted Hide Post
That's great news. Cool I'll be sure to always use an indexed target join field or a column in an RDBS when I use one.

Thanks to everyone for your help!


WebFOCUS 7.6.8; Windows
FOCUS 7.1.1; z/OS 1.09.00
 
Posts: 3 | Registered: July 17, 2009Report This Post
Member
posted Hide Post
I was so focused ( Wink) on my original question that, until I got an answer, I didn't think about asking if many-to-many joins could also be valid if 'TO ALL ' is used. Any thoughts on this one?

(If I should have posted this to a new question, please let me know and I'll do so).

Thanks!


WebFOCUS 7.6.8; Windows
FOCUS 7.1.1; z/OS 1.09.00
 
Posts: 3 | Registered: July 17, 2009Report This Post
Member
posted Hide Post
Remember when summing a numeric field in the crossreference file you can experience the "multiplicative effect". For each occurence of the host record it will retrieve the crossreference record and add them together. Use any prefix operator on the crossreferenced field to stop the multiplicative effect.


WebFOCUS 7.6.7
Windows
Excel, pdf, HTML
 
Posts: 1 | Location: United States | Registered: April 19, 2009Report This Post
Expert
posted Hide Post
The same rules apply for many to many.

Its the same an a bundle of one to many joins.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Guru
posted Hide Post
I've stumbled many times with many-many joins, when I didn't have enough join-fields from both files to make them 1:n or n:1 joins. The results are not always as expected depand on what the selection criteria. The report could be working for months and all of sudden it is wrong. I think it is because n:n gives false match rather than missing on a left-outer joins, and you couldn't filter with missing records. So I have to have additional passthrus on one or both files to make sure 1:n or n:1 joins. Am I missing something?

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Many-to-one JOIN - is this valid?

Copyright © 1996-2020 Information Builders