Add a Where Clause to a Query
This page last changed on Nov 28, 2008.
Oracle Data Service Integrator Documentation > Data Services Developer's Guide Add a Where Clause to a QueryThis topic describes several ways of adding XQuery where clauses to queries to join relational data sources.
Define the ConditionA 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 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 ClauseThe 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:
Use an XQuery Function in a Where ClauseA 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:
See AlsoHow Tos
|
Document generated by Confluence on Jan 13, 2009 15:57 |