Add a Where Clause to a Query 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 # Add a Where Clause to a Query This topic describes several ways of adding XQuery where clauses to queries to join relational data sources. Define the Condition Join Tables with a Where Clause Use an XQuery Function in a Where Clause See Also ## Define the Condition A where clause in XQuery specifies criteria defining some return data. This is a simple XQuery where clause:
    where $CUSTOMER/CUSTOMER_ID = "1111"
A where clause is usually part of an XQuery FLWOR (for-let-where-order by-return) expression. The where clause can be any XQuery expression, including another FLWOR expression. A common use of a where clause is to join two relational data sources, for example:
    for $CUSTOMER_ORDER in cus1:CUSTOMER_ORDER()
    where $CUSTOMER/CUSTOMER_ID eq $CUSTOMER_ORDER/C_ID
    return
    ... xml elements here ...
The where clause here specifies a condition that defines a subset of results to return. The SQL statement Oracle Data Service Integrator generates from this XQuery expression creates a left outer join between two tables:
    SELECT t1."CUSTOMER_ID" AS c1, t1."FIRST_NAME" AS c2, t1."LAST_NAME" AS c3, t1."SSN" AS c4,
      t2."C_ID" AS c5, t2."ORDER_ID" AS c6, t2."STATUS" AS c7, t2."TOTAL_ORDER_AMT" AS c8
    FROM "RTLCUSTOMER"."CUSTOMER" t1
    LEFT OUTER JOIN "RTLAPPLOMS"."CUSTOMER_ORDER" t2
    ON (t1."CUSTOMER_ID" = t2."C_ID")
    ORDER BY t1."CUSTOMER_ID" ASC
4. Before you add a where clause to a logical data service, think about how to structure it. If you want to join two data sources, you can only do so on a key field that appears in both. In this example, the CUSTOMER table has a primary key named CUSTOMER_ID joined to a CUSTOMER_ORDER table with a foreign key named C_ID. ## Join Tables with a Where Clause The simplest way to create a where clause between two relational data sources is to map it in Query Map view. To map the where clause: Open a logical data service in Workshop for WebLogic. Click Query Map. Drag the read functions of at least two physical data sources from Project Explorer to the Query Map view. In Query Map view, drag from a key element in the first data source to the corresponding key element in the second. If you click the second data source, you see the XQuery where clause in the expression editor:
       Where $CUSTOMER/CUSTOMER_ID eq $CUSTOMER_ORDER/C_ID
8. ## Use an XQuery Function in a Where Clause A where clause can also contain an XQuery function, including any built-in or Oracle-defined functions available from the Design Palette. The where clause is defined on an element within a For node. To create a where clause with an XQuery function: Click Query Map. Click the For title bar of the node that contains the element. Click Add Where Clause to insert the where clause. Open the Design Palette (Window > Show View > Design Palette). Expand XQuery Functions, then choose a function (for example: Duration, Date, and Time Functions > fn:year-from-date). Drag the function to the expression editor. Delete $arg in the function, then click the element in the For node that you want to add. Add an operator and a value to complete the expression.
       fn:year-from-date($CUSTOMER/CUSTOMER_SINCE) < 2000
9. You can use any of the XQuery operators available in Design Palette > XQuery Operators. Click Save . In Source view, the where clause in the read function looks like this:
       declare function tns:read() as element(tns:CUSTOMER_PROFILE)*{
       for $CUSTOMER in cus1:CUSTOMER()
       where fn:year-from-date($CUSTOMER/CUSTOMER_SINCE) < 2000
       return
       ... xml elements here ...
Test the query in Test view, preferably on sample data, to make sure the results are what you expect. ## See Also ##### How Tos Test a Read Function and Simple Update Test a Create or Delete Procedure

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