Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Many-to-one JOIN - is this valid?
Go
New
Search
Notify
Tools
Reply
  
[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, 2009Reply With QuoteReport 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, 2008Reply With QuoteReport 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, 2009Reply With QuoteReport 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, 2007Reply With QuoteReport 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: 142 | Location: Adelaide South Australia | Registered: October 27, 2006Reply With QuoteReport 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.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Or the two files/table must be sored in he same order.
 
Posts: 142 | Location: Adelaide South Australia | Registered: October 27, 2006Reply With QuoteReport 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, 2009Reply With QuoteReport 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, 2009Reply With QuoteReport 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, 2009Reply With QuoteReport 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.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport 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, 2008Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.