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,

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.

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.

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,

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.

quote:

All I know is that whenever I remove it from my join

by "it", you mean the MULTIPLE or UNIQUE or something else?

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.

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.

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,

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.

Thanks. I'll go with this...