 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [SOLVED] JOIN - 'one-to-one' acting like 'one-to-many'
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] JOIN - 'one-to-one' acting like 'one-to-many' Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] JOIN - 'one-to-one' acting like 'one-to-many' | Login/Join |
| Member |
I am doing a simple join between 2 DB2 tables in Mainframe Focus 7.3.3. First table has one record for each vendor, cross-referenced file has multiple records per vendor. I only want one of the records off the CR file, so I am not using 'ALL' in the JOIN. However, output is displaying all the joined records from the CR file. Any suggestions please? JackieThis message has been edited. Last edited by: Kerry, WebFocus 7.1.4, ZOS / USS, Output formats: HTML, Excel, PDF..... Mainframe Focus 7.3.3 | ||
|
| Virtuoso |
As far as I understand, that behaviour is only valid when dealing with FOCUS files, not with DBMS tables. So, if in your underlying data you have a parent with more than 1 child record, all of those children will be returned by means of the SQL join structure being sent to DB2. A SQL join structure "links" 2 tables and ALL matching records are retrieved; there is no consideration for One-to-One or One-to-Many limitation in the SQL structure itself; the database engine will always return everything that matches (with the exception of outer joins which behave like Focus "short paths" but that's an entirely different topic).This message has been edited. Last edited by: njsden, Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2 Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K. | |||
|
| Virtuoso |
There could be different approaches to what you need. One would be to retrieve your data as is (that is, multiple records per vendor) sorted by vendor and to save it in a HOLD file. You can then DEFINE a record counter field (REC_COUNTER/I4) that resets to 1 whenever a new vendor is found. Finally, use TABLE FILE ... WHERE REC_COUNTER EQ 1 and that would give you what you need. You can attempt to define something at the database layer such as: sub-queries or analytic functions if supported by DB2 and use them via SQL passthru or through database views, but the first approach might be the easiest one to implement. Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2 Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K. | |||
|
| Virtuoso |
Another option you can try (depending on how complex your request is) is to use a SUM verb where you display the first instance of each record found for each vendor: TABLE FILE blah SUM FST.field1 FST.field2 . . BY VENDOR . END Keep in mind though that such a logic cannot be resolved by the database directly but internally by FOCUS so performance may not be that great. You can of course, retrieve the minimum data required from the database (this will still give you as many child records as found), HOLD it and them attempt the SUM FST.field1 BY vendor approach out of the HOLD file itself. Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2 Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K. | |||
|
| Virtuoso |
From the documentation:
If there is some way to distinquish one detail record from the next, you can use a WHERE clause to select only the desired detail record. Otherwise, you are left with some of the ideas that njsden has suggested above. WebFOCUS 7.7.05 | |||
|
| Member |
Thanks guys for all of your helpful suggestions. You won't believe this, but after reading your replies I remembered that I had gotten this functionality to work on our production system. Now I was running my jobs on our test system. Same job, two different locations, two different outputs. I discussed the issue with one of our mainframe DBA's, and she suggested I try the system programmer who handles Focus/WebFocus. After I started describing my problem to him, the light bulb went on over his head, and he said 'Oh, I deleted that parm this week because I didn't know what it did' (while trying to fix something else). He put the parm 'SET ALL=ON' back into the test profile, and my problem disappeared!  Thanks again for your assistance, it helped direct me to a successful resolution! Jackie WebFocus 7.1.4, ZOS / USS, Output formats: HTML, Excel, PDF..... Mainframe Focus 7.3.3 | |||
|
| Virtuoso |
 You just turn your inner join into an outer join. How does that "fix" the situation when you need to retrieve only one child record even when 2 or more exist for the same parent?Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2 Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K. | |||
|
| Expert |
I'm just guessing here but it probably 'works' because the SET statement turned off optimization (you'd have to do a trace to verify) and WebFOCUS wound up doing the join and doing the way it was requested. I have a major problem with putting SET ALL=ON in a global profile expecially where there are a lot of relational queries being done. More often than not, this statement turns off optimization thereby reducing performance and efficiency because you are not allowing WebFOCUS to pass the SQL query to the backend data base. Each developer should make that decision for each program. Jackie, I recommend that you take the advice previously given and take all the data sent back to you. You can use a compute or a sum to restrict the printing to only the first child segment returned per parent. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
| Member |
SOLVED WebFocus 7.1.4, ZOS / USS, Output formats: HTML, Excel, PDF..... Mainframe Focus 7.3.3 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] JOIN - 'one-to-one' acting like 'one-to-many'
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] JOIN - 'one-to-one' acting like 'one-to-many'Copyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |