EQL operators for filterString filters

The WHERE expression uses one or more attributes whose values are to be tested, and one or more test conditions. For example, the expression can use numeric and string value comparison operators, NULL value evaluation operators, and logical operators, as well as some functions. Note that unlike an EQL statement, the WHERE keyword itself is not used in the query string.

The following table lists the operators that can be used in an EQL record filter expression:

Operator	Description	Example
=	Equal (tests the equality between two expressions)	COUNTRY_NAME = 'France'
<>	Not equal (tests the condition of two expressions not being equal to each other)	PROD_WEIGHT_CLASS <> 2
>	Greater than (tests the condition of one expression being greater than the other)	PROD_MIN_PRICE > 1000
<	Less than (tests the condition of one expression being less than the other)	QUANTITY_SOLD < 500
>=	Greater than or equal (tests the condition of one expression being greater than or equal to the other expression)	PROD_MIN_PRICE >= 75
<=	Less than or equal (tests the condition of one expression being less than or equal to the other expression)	PROMO_COST <= 1500
BETWEEN low AND high	Specifies an inclusive range of values. Use AND to separate the low (starting) and high (ending) values.	FISCAL_YEAR BETWEEN 2000 AND 2006
IS NULL	Specifies a search for NULL values in a single-assign attribute.	CUST_EMAIL IS NULL
IS NOT NULL	Specifies a search for values that are not NULL in a single-assign attribute.	PROD_STATUS IS NOT NULL
IS EMPTY	Specifies a search for an empty set (i.e., from a multi-assign attribute).	LOCALES IS EMPTY
IS NOT EMPTY	Specifies a search for a non-empty set (i.e., from a multi-assign attribute).	LOCALES IS NOT EMPTY
AND	Combines two conditions and evaluates to TRUE when both of the conditions are TRUE.	PROD_MIN_PRICE > 1000 AND COUNTRY_NAME = 'Spain'
OR	Combines two conditions and evaluates to TRUE when either condition is TRUE.	PROD_LIST_PRICE > 50 OR PROD_CATEGORY = 'Hardware'
NOT	Reverses the value of any Boolean expression.	NOT(COUNTRY_REGION = 'Europe' AND AMOUNT_SOLD > 1000)

Note that you cannot use aggregating functions (such as SUM) in the query.

Syntax for single-assign versus multi-assign attributes

The EQL syntax for the WHERE expression will depend on whether an attribute is configured as a single-assign or multi-assign attribute. For example, if the Flavors attribute is a single-assign string attribute, then this comparison syntax would work:

<filterString>Flavors = 'Peach'</filterString>

But if Flavors is a multi-assign attribute, then that syntax would fail with this error message:

Cannot compare mdex:string-set and mdex:string

The reason for the error is that in EQL a multi-assign attribute is treated as a set (of data type mdex:string-set), and a string (such as 'peach') cannot be compared to a string-set. (In other words, in EQL a single-assign attribute is of data type mdex:string while a multi-assign attribute is considered as being of data type mdex:string-set.) Thus, the expression would have to use the multi-assign syntax, such as these three examples:

<filterString>SOME i IN Flavors SATISFIES (i = 'Peach')</filterString>

<filterString>IS_MEMBER_OF('Peach', Flavors)</filterString>

<filterString>'Peach' IN Flavors</filterString>

This caveat for single-assign versus multi-assign attributes applies to all data types. For more information on working with multi-assign data in EQL, see the Oracle Endeca Server EQL Guide.

Using single quotes with string values

When using string value comparison operators, make sure that you use single quotes around the text value field. For example, if the COUNTRY_NAME standard attribute is of type mdex:string, then the usage would be:

COUNTRY_NAME = 'Spain'  // Correct

COUNTRY_NAME = "Spain"  // Incorrect because double quotes are not allowed

COUNTRY_NAME = Spain  // Incorrect because the attribute stores string values

Also keep in mind that string comparisons are case-sensitive. Thus:

COUNTRY_NAME = 'spain'

would not match if all COUNTRY_NAME values were "Spain" (i.e., no "spain" values).

When using numeric value comparison operators, do not use quotes of any kind around the value field. For example, if the AMOUNT_SOLD standard attribute is of type mdex:double, then the usage would be:

AMOUNT_SOLD = 500  // Correct

AMOUNT_SOLD = "500"  // Incorrect because the attribute stores numeric values

Escaping special XML characters

If you are making direct queries against the Conversation Web Service (for example, by using the soapUI tool), you may need to escape some XML characters to prevent parsing errors. For example, you should use the < escape character instead of the < (less than) character. Note that examples in this section will use the unescaped version for ease of reading.