eDocs Home > BEA AquaLogic Data Services Platform Documentation > Data Services Developer's Guide > Contents

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

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 ALDSP 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 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:

1. Open a logical data service in Studio.
2. Click Query Map.
3. Drag the read functions of at least two physical data sources from Project Explorer to the Query Map view.
    

   

4. 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

Use an XQuery Function in a Where Clause

A where clause can also contain an XQuery function, including any built-in or BEA-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:

1. Click Query Map.
2. Click the For title bar of the node that contains the element.
3. Click Add Where Clause to insert the where clause.
4. Open the Design Palette (Window > Show View > Design Palette).
5. Expand XQuery Functions, then choose a function (for example: Duration, Date, and Time Functions > fn:year-from-date).
6. Drag the function to the expression editor.
7. Delete $arg in the function, then click the element in the For node that you want to add.
8. Add an operator and a value to complete the expression.

   fn:year-from-date($CUSTOMER/CUSTOMER_SINCE) < 2000

   You can use any of the XQuery operators available in Design Palette > XQuery Operators.

9. 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 ...

10. 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