I'm new to joining tables in IBI's Developer Studio and I'm trying to understand the purpose of the One-to-One option. I have two tables with a one to many relationship that I am using for my test. I've created a synonym that joins the two tables together with the One-to-One option selected. When I run a report against this synonym, I get all the records from the table on the right side of the join, not just one. In fact, it seems to generate the same SQL whether I have the synonym defined as One-to-Many or One-to-One. This option doesn't seem to do anything. Does anyone know what it's supposed to do?

My synonym is defined as follows:
FILENAME=vw_rapid7_test, $
SEGMENT=MSQL_RAPID7_NXP_SCANCFGS, CRFILE=ITADMINREPORTING/MSQL_RAPID7_NXP_SCANCFGS, CRINCLUDE=ALL,
DESCRIPTION='Scan configurations', $
SEGMENT=MSQL_RAPID7_NXP_SCANS, SEGTYPE=KU, PARENT=MSQL_RAPID7_NXP_SCANCFGS, CRFILE=ITADMINREPORTING/MSQL_RAPID7_NXP_SCANS, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
JOIN_WHERE=MSQL_RAPID7_NXP_SCANCFGS.SCAN_CONFIG_ID EQ MSQL_RAPID7_NXP_SCANS.SCAN_CONFIG_ID;,
DESCRIPTION='Scan results', $

This message has been edited. Last edited by: Amy@OD,

quote:

SEGTYPE=KU

is how the .mas file reflects that you've declared a "unique" join. (For "multiple" it would be KM)

If based on the primary key structure of the "right-hand" table (reflected in the KEYS= attribute of the .acx file) WebFOCUS concludes that the basis for the join (JOIN_WHERE=) should yield a unique right-hand instance for each left-hand instance, WF will run with that.

If not, WF will generate a SQL Select ordered in such a way that it can detect multiples, and discard all but the first.

So even if the SQL is identical for your two join structures, the net answer-set produced by WF could differ.

The converse may also be true:

I think you're telling us that SCAN_CONFIG_ID is not the primary key of the MSQL_RAPID7_NXP_SCANS table, and multiple rows may share a common value for that column.

If the KEYS= entry in the .acx, and list of FIELDs in the .mas, indicate that SCAN_CONFIG_ID constitutes the primary key, WebFOCUS will rightly conclude that the condition:

JOIN_WHERE=MSQL_RAPID7_NXP_SCANCFGS.SCAN_CONFIG_ID EQ MSQL_RAPID7_NXP_SCANS.SCAN_CONFIG_ID;,

covers the entire primary key of the second table, so WebFOCUS will issue a simple join without taking steps to detect and discard supernumerary matches.

The left-hand table contains SCAN_CONFIG_ID as the primary key. The right-hand table has a different primary key (SCAN_ID). The right-hand table contains multiple SCAN_CONFIG_ID records to every one SCAN_CONFIG_ID record in the left table. Under what join condition would WebFocus only return one record from the right table for every record in the left table?

Are the tables relational that you are trying to join in the synonym? If so, then one-to-one, in a true one-to-many will never return just one row (providing the relational engine is handling the join... which you want for efficiency). Relational just doesn't see one-to-one unless it really is a one-to-one. If you have to have just the first record from the join, I would do it with a match, or force WebFOCUS to do the join (at the sacrifice of efficiency).

Thank you Emily for your post. Just to clarify...are you saying that the One-to-Many / One-to-One options in the synonym editor don't actually do anything if I am using a relational database such as Microsoft SQL Server?

When a relational database joins 2 data sources, it will retrieve ALL matching rows that satisfy the join condition, so if you have a Invoice ID#25 with 3 items, joining INVOICE to ITEMS where ID=25 will always give you 3 rows; you can't force it to behave as a "one-to-one" and give you only the very first instance of the result for each different ID unless you write the query yourself and use analytics or other database features.

One-to-one is a special type of JOIN that WebFOCUS implements only on certain data sources that support it (such as FOCUS databases) but when it comes to relational databases which offer no support for it, WebFOCUS leaves the responsibility to resolve the join to the database and in this case one-to-one behaves the same as one-to-many. Think of "many" as 0, 1 or more matching rows.

I'm sure I saw a good explanation in one of WebFOCUS guides but can't remember which one.

Amy,

That is correct. While the synonym editor has the one-to-one option, it only is effective for certain database types. njsden explained it really well. Relational databases just don't see a relationship as one-to-one. They see it as one-to-many that just happens to only have one matching ocurrance. So, as I said before. If you have to have that behavior, there are various techniques that might help you return only the first row (which may or may not translate to SQL, so may or may not be efficient). Or force WebFOCUS to do the join of the two tables (not generally recommended, but if your tables are small, then not a big deal), in which case the one-to-one would be honored.

I think that a point not made clearly is that the underlying tables could be a one-to-one relationship, if the database designers wanted that to be the case, using a single unique primary key.

That there can be more than one row of data for each MSQL_RAPID7_NXP_SCANS.SCAN_CONFIG_ID is by design; it would therefore be logically incorrect for WebFOCUS to force the return of a single row, even if SQL allowed it, which it doesn't.

Which row in MSQL_RAPID7_NXP_SCANS should be returned by a pseudo one-to-one JOIN?

If you can answer that, then you can probably create a request to return that specific row.

Not that simple.

I have managed to trigger each of the following errors, in reports on JOINs of Oracle tables:


(FOC2523) MULTIPLICATIVE EFFECT MAY CAUSE DUPLICATED LINES IN REPORT
A FOCUS procedure was executed which contained a PRINT or LIST verb on a segment level in the structure where duplicate rows could be generated, causing duplicated lines to be displayed in the report. The effect of this condition will vary depending on the setting of SQL SET OPTIMIZATION.

(FOC2524) JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
A FOCUS procedure was executed on a unique JOIN structure that did not specify all the field components in the primary key of the cross-referenced file. The effect of this condition will vary depending on the setting of SQL SET OPTIMIZATION.

(FOC2525) FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: %1%2
This message is used to enumerate the segments which caused the issuance of a FOC2524 message during a FOCUS procedure on a unique JOIN structure. The effect of this condition will vary depending on the setting of SQL SET OPTIMIZATION.



TABLE will analyze a declared Unique Join between tables of a relational database, to verify whether the Primary Key structure (reflected in the synonym's KEYS attribute and the corresponding leading FIELD declarations) guarantees the stated uniqueness.

In some cases, when it cannot "guarantee" uniqueness, I found the navigation will be partially optimized: the joins and sorting (and perhaps aggregation) will be passed to the database server, but the answer-set returned by the database server will be post-processed by TABLE to eliminate spurious duplicates (and perhaps aggregate results). It all depends on the details (the join predicate, the synonym, OPTIMIZATION setting, and perhaps the particular RDBMS).

In 7.7.03, I came across cases where uniqueness was logically guaranteed by the structure of the underlying tables, but TABLE was not sufficiently smart (or informed) to deduce that. In one case, where the join covered what I knew was an "alternate" business key equivalent to the primary key, I broke up a major reporting logjam by using an alternate synonym that painted the alternate as the primary key -- but only after verifying my assumptions with the DBA.

This message has been edited. Last edited by: j.gross,