Extended Query Conditions

When writing table component code, let the QueryExe() method evaluate query conditions that are stored in an incoming XML document, and reduce the number of records returned to the Rules Engine.

Background

By default, when the Rules Engine passes a query to a table component, only query conditions with the form:

are passed to the component. All other conditions, like:

birth_date > '01-jan-1859'

last_name != 'Hayes'

last_name LIKE 'A%'...

are NOT passed to the component. As a result, the component returns more records than queried, and the Rules Engine evaluates the other conditions. This may result in bad performance.

However, the Rules Engine can translate the contents of an SQL WHERE clause into a single XML document, and pass this XML document in the CONDITIONS parameter of the QUERYEXE method to the component. This way, the component can evaluate some or all query conditions, and reduce the number of records returned to the Rules Engine.

IMPORTANT: The Rules Engine always checks the returned records a second time.

Only those conditions that the component can safely use to reduce the number of records are added to the XML document. Conditions that contain a reference to a column of another table are not added. If a condition is nested with a condition on another table, the Rules Engine examines if the condition can safely be passed to the component or not. Valid records are never rejected.

The syntax of the XML document is described in the related help topic: "The Extended Query Conditions XML Document".

When to use

Use the extended query conditions mechanism if:

•Querying the external data source takes a lot of time.

•The component table is expected to be queried often with conditions other than <column> = <value>.

To use extended query conditions:

1.	In the Definer, open the appropriate table component.

2.	Click the State tab page.

3.	Select the Extended query conditions check box.

4.	(Re-)write the QueryExe() method in the component code, and let this method evaluate the expected query conditions.

Example 1

Consider the following query on an EMPLOYEE component table:

SELECT *

FROM employee

WHERE id > 10

AND ( name NOT LIKE 'A%' OR name = 'JOHN' )

This WHERE clause is translated into XML document:

Conditions passed in XML:

<?xml version = "1.0"?>

</Operator>

</Operator>

</Operator>

</LogicalOperator>

</SqlNode>

Example 2

Consider the following query on an EMPLOYEE component table with a join to an ORDER database table. The Rules Engine only extracts the conditions that the component can evaluate.

SELECT *

FROM employee e

, order o

WHERE e.id = o.emp_id

AND

( o.status = 'OPEN'

OR e.name = 'MIKE'

)

AND e.id > 10

AND

(

e.name NOT LIKE 'A%'

OR e.name = 'JOHN'

)

In this case, the same (last two) query conditions are passed to the component as in the previous example.