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     [CLOSED] JOIN MULTIPLE V. UNIQUE?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] JOIN MULTIPLE V. UNIQUE?
 Login/Join
 
Expert
posted
My basic question is, based on a scenario:

Scenario: The fields in the source files which are used in the JOIN statement, FROM and TO, do not have duplicate values, only unique values.

Question: Based on that scenario, is it relevant to a JOIN statement if MULTIPLE is used instead of UNIQUE.

Thought: It does not matter.

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




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
The answer depends upon the type of data files being joined.

If FOCUS or XFOCUS db are used, then a UNIQUE JOIN assumes that a record exists, the child is deemed a unique segment and will always exist, values of blank or zero will be displayed if the child does not exist. If MULTIPLE/ALL is used then the behaviour is further controlled by the SET ALL parameter. If SET ALL=OFF, then a parent will not be displayed if a child does not exist (a short path), if SET ALL=ON, then a blank/zero record for a child that is non-existent will be displayed. When SET ALL=PASS then any screening condition on a non-existent child will pass this screening and the parent displayed.

If relational tables are used then the behaviour then these are normally translated into an inner or left outer join.

IMHO it always matters which is used.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Master
posted Hide Post
Multiple V. Unique is one of those irritating things that WebFOCUS does which can cause odd results and changes the way SQL is translated sometimes. The concept of Multiple and Unique is not one present in SQL (at least not in Oracle, i'm pretty sure in DB2 MSSql etc as well) At the end of the day, if your code is translating correctly into SQL and being completely executed in the DB, then multiple V unique wont matter. If your query can't translate to SQL properly and WF does a WebFOCUS Managed Join (this can also happen when it can't do aggregation properly etc.), then Multiple V Unique will cause your data to join differently. I think the way unique works is that when a value in the parent finds its match in the child table, it will no longer match future instances of that value. Its very frustrating and causes wholes in your data.

In 7703, when creating synonyms and business views, we ran into a situation where we needed to define all of our joins to Unique because there was an issue with Left Outer joins otherwise (Its been a while so my recollection is fuzzy). Outside of synonyms I ALWAYS remove multiple/unique from my joines. If I could do it in my synonyms then I would.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Expert
posted Hide Post
Elaborations:
1) These are both single segment files and the fields being joined are, we'll say, somewhere in the middle of the segment of each file and they are not indexed but are in descending sort order.
2) This creates the SQL code fine. But then, wait for this..., it passes that to 1010data for the creation of the 1010data Macro Code. 1010data says it's doing that part correctly.

quote:
Alan: IMHO it always matters which is used.
I totally agree. So, knowing which one to use is of utmost importance.
quote:
Eric: I think the way unique works is that when a value in the parent finds its match in the child table, it will no longer match future instances of that value.
And similarly, in a MULTIPLE, if the "TO" file is not in sorted order, then it will no longer match future instances of that value, right?

This message has been edited. Last edited by: Doug,
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
Doug,

Unfortunately I don't know anything about 1010Data, and Alan is probably a much better resource for the ins and out of Multiple/Unique. All I know is that whenever I remove it from my join statements I get better results then when I have it in there. Its just frustrating when you think you you have the correct results only to find that the query got forced to a WebFOCUS managed join and your result is completely wrong because it did a unique join in WF land instead of multiple (even more frustrating when you can't change your synonym to multiple and are forced to unique). If it were my choice I would throw out the concept completely.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Expert
posted Hide Post
quote:
All I know is that whenever I remove it from my join
by "it", you mean the MULTIPLE or UNIQUE or something else?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
When I write my joins, I never specify multiple vs unique. Multiple is probably the default. I don't know, I never cared to look into it. If I use the GUI to create the join (Sometimes I don't want to write the file syntax when filtering in the Join), I always remove any reference to Unique / Multiple. In the synonym I don't have an option. I have to specify something. In my synonyms I use SEGTYPE=KU which is one to one. I'ld have to look up the case for why we couldn't use one to many, but there was a reason. I think it was that Left Outer joins were being forced to Inner Joins. I'm not sure if its still an Issue in WF8, but I don't have the desire to find out.

When I raised to them the issue that FOCUS managed joins would then treat those requests differently then database joins, the response was pretty much, as long as the SQL translates properly it won't be an issue....

So I do a lot of tracing when writing off of that masterfile to make sure that my requests translate properly to the database to avoid FOCUS managed joins.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Virtuoso
posted Hide Post
Unfortunately JOIN cannot now be explained easily in a couple of sentences.

Prior to SQL & relational tables, JOIN was a simple concept:

JOIN field IN file TO [ALL] field IN file AS joinname

with

SET ALL = OFF|ON|PASS

The SET ALL being directly related to the word ALL in the JOIN syntax.


As JOIN was then used for SQL, the syntax has evolved. Now the syntax includes:

LEFT_OUTER
INNER
UNIQUE
MULTIPLE
SET MULTIPATH
SET JOINOPT
SET SHORTPATH
SQL target_db SET SQLJOIN

some of these are synonymous with the old syntax, but also do clarify the JOIN syntax.

Mostly, I think, perceived (or real) issues arise with LEFT_OUTER not INNER, and the syntax reflects this.

LEFT_OUTER options are almost fully explained in this documentation.

However back to the original question, as this is a non-FOCUS SQL based source, then the rules for JOINs that apply to FOCUS data sources (e.g. HOLD files) do not apply, JOINs should be passed to the underlying engine to handle; sort order within the tables is immaterial. Also avoid WebFOCUS handled joins, these just cannot perform as well as SQL engine handled joins.

The question becomes how you want short paths handled, this is more important than just using UNIQUE.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
Thanks Eric... I work on this and see what happens... I may ask Renee about it...

This message has been edited. Last edited by: Doug,
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Platinum Member
posted Hide Post
There was a webcast on the subject of joins last week: "Relational Efficiencies - Part I: JOINing It All Together" presented by Renee Teatro.

The recording can be found here

It contains the recording of the webcast, plus a Word document which already explains the different kind of joins extensively, including all the trace options to see how WebFocus code is translated (or not) into SQL.

Martin.


WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report This Post
Expert
posted Hide Post
Thanks. I'll go with this...




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report 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     [CLOSED] JOIN MULTIPLE V. UNIQUE?

Copyright © 1996-2020 Information Builders