Handle Non-Unique Joins This page last changed on Nov 28, 2008. .stickable, h1, h2, h3, h4, h5 false true Show Sticky notes Hide Sticky notes Save Cancel Oracle Data Service Integrator Documentation > Data Services Developer's Guide # How To Handle Non-Unique Joins This topic shows how to enable an update map when a logical data service uses a non-unique join between relational data sources. Understand the Join Correct the Block Scope Correct the Table Join Enable Update Blocks and Procedures Test a Non-Unique Join See Also ## Understand the Join In 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 You can also create a join by adding an XQuery WHERE statement in the expression editor or the Source tab:
    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:
CUSTOMER_ID | FIRST_NAME | LAST_NAME | EMAIL_ADDRESS | ORDER_ID | ORDER_DT | TOTAL_ORDER_AMT
--- | --- | --- | --- | --- | --- | ---
CUSTOMER1 | Jack | Black | jack@yahoo.com | ORDER_1_0 | 2001-10-01 | 156.39
CUSTOMER1 | Jack | Black | jack@yahoo.com | ORDER_1_1 | 2002-02-17 | 596.65
CUSTOMER1 | Jack | Black | jack@yahoo.com | ORDER_1_2 | 2002-07-07 | 656.65
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 Block When 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: A logical data service with a flat (non-nested) return type. An incorrect block scope in the query map. An incorrect table join, or no table join, in the query map. An attempt to join on a field other than a key field. ## Correct the Block Scope If your logical data service has a nested XML return type, scope the data sources to XML blocks within the return type. In Query Map, click the zone icon of a data source. Drag the zone icon from the data source to the nested element in the return type. Mouse over the zone icon in the data source. Verify that only the nested element is highlighted in the return type. ##### Checking the Scope in the Return Type ## Correct the Table Join You 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: Click the Query Map tab. Drag from a key element in one data source to the same key element in another data source (for example, $CUSTOMER/CUSTOMER_ID to $ADDRESS/CUSTOMER_ID). Click the Source tab and expand the read function to check the location of the WHERE clause. For example, if your XML return type is nested, the XQuery code should also be nested:
    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: Click the Query Map tab. Click the For block of the data source you are joining to. In the expression editor, click Add Where Clause . After the Where keyword, add the elements to be joined (for example, $CUSTOMER/CUSTOMER_ID eq $CREDIT_CARD/CUSTOMER_ID). Click Save . Check the WHERE clause in the Source tab, as described above. 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 Procedures If 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: Right-click in the block, and choose Enable. The update block should now have a white (enabled) background. The Create, Update, or Delete procedure icons might still appear red or yellow, if they are disabled. However, you should be able to test the primary read function. Click the Test tab. At Select Operation, choose the primary read function, and click Run. To enable an update map procedure: If an element is marked with a Warning icon indicating that a mapping is required, select it. In the expression editor, give the element a value with the correct data type. Continue for all disabled elements. In the Test tab, test an update procedure to ensure that the value overrides you have entered do what you want. ## Test a Non-Unique Join Let'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):
CUSTOMER_ID | FIRST_NAME | LAST_NAME | EMAIL_ADDRESS | ORDER_ID | ORDER_DT | TOTAL_ORDER_AMT
--- | --- | --- | --- | --- | --- | ---
CUSTOMER1 | Jack | Black | jack@yahoo.com | ORDER_1_0 | 2001-10-01 | 156.39
CUSTOMER1 | Jack | Black | jack@yahoo.com | ORDER_1_1 | 2002-02-17 | 596.65
CUSTOMER1 | Jack | Black | jack@yahoo.com | ORDER_1_2 | 2002-07-07 | 656.65
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: Click a row in the joined table data, then click Edit. Locate the correct node in the XML tree data, and expand it. Click the value you want to change, then edit it. Click Submit. 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 Also ##### Concepts Recognize When Something is Wrong ##### How Tos Test a Read Function and Simple Update

Document generated by Confluence on Jan 13, 2009 15:57