Handle Non-Unique Joins
This page last changed on Nov 28, 2008.
Oracle Data Service Integrator Documentation > Data Services Developer's Guide How To Handle Non-Unique JoinsThis topic shows how to enable an update map when a logical data service uses a non-unique join between relational data sources. Understand the JoinIn a logical data service, you can join tables visually in the Query Map by dragging from a key element in one data source to a corresponding key element in another data source. Joining Tables in the Query Map
where $CUSTOMER/CUSTOMER_ID eq $CREDIT_CARD/CUSTOMER_ID If both tables are in the same database, the XML return type is nested, and you are joining on a unique key, Oracle Data Service Integrator creates a left outer join. You can see the SQL in the query plan for the service (click the Plan tab, then Show Query Plan): SELECT ... FROM "RTLCUSTOMER"."CUSTOMER" t1 LEFT OUTER JOIN "RTLCUSTOMER"."ADDRESS" t2 ON (t1."CUSTOMER_ID" = t2."CUSTOMER_ID") If the XML return type is flat, Oracle Data Service Integrator creates an inner join, and the SQL looks like this: SELECT ... FROM "RTLAPPLOMS"."CUSTOMER_ORDER" t1 JOIN "RTLCUSTOMER"."CUSTOMER" t2 ON (t2."CUSTOMER_ID" = t1."C_ID") A left outer join returns rows from the left (meaning, the first) table, even if they do not match any rows in the right (second) table. An inner join requires that a value in the left table match a value in the right table in order for the left values to be included in the result. For example, you might match one customer to many orders, creating a joined table like this:
Here, CUSTOMER_ID is a unique key and has one row in the relational source. However, in the joined table, CUSTOMER1 has three orders and three rows. If you update information for CUSTOMER1 such as FIRST_NAME in the joined table, where each customer has multiple rows, the value to use to update the underlying data source is ambiguous. With a non-unique join, all or part of the update map is temporarily disabled and looks like this: A Disabled Update BlockWhen you click View Generate Log in the update map, you see a message like this one: The primary read function has a non-unique join involving this data source.
In your function or procedure code, in the Source tab, you might see for statements directly nested within each other, without an intervening WHERE clause: for $CUSTOMER in ns1:CUSTOMER() for $CREDIT_CARD in ns2:CREDIT_CARD() return Or, you might see XML elements directly nested within each other without intervening SQL statements: <ns7:CUSTOMER_PROFILE> <CUSTOMER> ... { <CREDIT_CARD> ... </CREDIT_CARD> } </CUSTOMER> </ns7:CUSTOMER_PROFILE> These are all symptoms of a non-unique join. You need to enable the update map so that you can deploy the service, test it, and make it available to client applications. In an update map, the most common causes of a non-unique join are:
Correct the Block ScopeIf your logical data service has a nested XML return type, scope the data sources to XML blocks within the return type.
Checking the Scope in the Return TypeCorrect the Table JoinYou might also get a non-unique join if the data sources are not joined correctly. You can join the tables either visually in the Query Map or by entering a WHERE clause in the expression editor or the Source tab. Be sure to join tables on a key element, marked like this: To join tables visually:
for $CUSTOMER in ns1:CUSTOMER() return ... for $CREDIT_CARD in ns2:CREDIT_CARD() where $CUSTOMER/CUSTOMER_ID eq $CREDIT_CARD/CUSTOMER_ID return ... To use the expression editor:
Remember that Oracle Data Service Integrator creates a left outer join if both tables are in the same database and the XML return type is nested. If the XML return type is flat, Oracle Data Service Integratorcreates an inner join. Enable Update Blocks and ProceduresIf your service has a return type with a flat structure, you may get a non-unique join, even if the join is correct in the Query Map and the Source tab. If this happens, or if all or part of the update map is disabled for any reason, you can enable an update block or the Create-Update-Delete procedures within the block. To enable a disabled (yellow) update block:
To enable an update map procedure:
Test a Non-Unique JoinLet's go back to the sample joined table data (which we can see in the Test tab, by choosing the primary read function and clicking Run):
In this case, the XML return type is flat, and Oracle Data Service Integrator has created an inner join between the CUSTOMER and CUSTOMER_ORDER tables in underlying relational data sources. In the joined table view, one customer has many orders. The CUSTOMER_ID can appear multiple times, but the ORDER_ID is unique. Once the update map is enabled, you can update data in either the CUSTOMER or CUSTOMER_ORDER table in the data sources:
If you update TOTAL_ORDER_AMT, from the CUSTOMER_ORDER table, the amount changes in one row of the joined table view. However, if you update EMAIL_ADDRESS, the email address changes in one row of the data source table and in all rows for that customer in the joined table view. See AlsoConceptsHow Tos |
Document generated by Confluence on Jan 13, 2009 15:57 |