Optional  An integer specifying an order-of-magnitude estimate of the number of elements in list. Must be specified as a literal with one of the following values: 10, 100, 1000, 10000, and so forth.

Description

The %INLIST predicate is an InterSystems IRIS extension for matching the values of a field with the elements of a list structure. Both %INLIST and IN allow you to perform such equality comparisons with multiple specified values. %INLIST specifies these multiple values as the elements of a single list argument. Therefore, %INLIST allows you to vary the number of values to match without creating a separate cached query.

The optional %INLIST SIZE clause provides the integer nn, which specifies an order-of-magnitude estimate of the number of list elements in list. InterSystems IRIS uses this order-of-magnitude estimate to determine the optimal query plan. Because the same cached query is used regardless of the number of elements in list, specifying SIZE allows you to create a cached query optimized for the anticipated approximate number of elements in list. Changing the SIZE literal creates a separate cached query. Specify nn as one of the following literals: 10, 100, 1000, 10000, etc. Because nn must be available as a constant value at compile time, it must be specified as a literal in all SQL code. Note that double parentheses must be specified as shown for all compiled SQL (Dynamic SQL). Double parentheses are not used with Embedded SQL. For further details, refer to the Cached Queries chapter in SQL Optimization Guide.

It is not meaningful to specify NULL as a comparison value. NULL is the absence of a value, and therefore fails all equality tests. Specifying an %INLIST predicate (or any other predicate) eliminates any instances of the specified field that are NULL. You must specify the IS NULL predicate to include fields with NULL in the predicate result set.

Like most predicates, %INLIST can be inverted using the NOT logical operator. Neither %INLIST nor NOT %INLIST can be used to return NULL fields. To return NULL fields use IS NULL.

If the match expression is not in %List format, %INLIST generates an SQLCODE -400 error. For example, if the SqlListType of the collection property is DELIMITED, the logical value of the list field is not in %List format. For further details on list structures, see the SQL $LIST function.

For matching a value to an unstructured series of items, such as a comma-separated list of values, use the IN predicate. IN can perform equality comparisons and subquery comparisons.

%SelectMode

The %INLIST predicate does not use the current %SelectMode setting. The elements of list should be specified in Logical format, regardless of the %SelectMode setting. Attempting to specify list elements in ODBC format or Display format commonly results in no data matches or unintended data matches.

You can use the %EXTERNAL or %ODBCOUT format-transform functions to transform the scalar-expression field that the predicate operates upon. This allows you to specify the list elements in Display format or ODBC format. However, using a format-transform function prevents the use of the index for the field, and can thus have a significant performance impact.

In the following Dynamic SQL example, the %INLIST predicate specifies a list containing date value elements for the year 1978 in Logical format, not in %SelectMode=1 (ODBC) format. Dates that correspond to these $HOROLOG format dates are selected:

The following Dynamic SQL example uses the %ODBCOUT format-transform function to transform the DOB field matched by the predicate. This allows you to specify the %INLIST list elements in ODBC format. However, specifying the format-transform function prevents the use of an index for DOB field values:

However, in Dynamic SQL you can supply the %INLIST predicate values as a single host variable; you must supply the IN predicate values as individual host variables. Therefore, changing the number of IN predicate values results in the creation of a separate cached query. Changing the number of %INLIST predicate values does not result in the creation of a separate cached query. For further details, refer to the Cached Queries chapter in SQL Optimization Guide.

Examples

The following example matches Home_State column values to the elements of a structured list of northern New England states:

The following two examples show that collation matching is based on the scalar-expression collation. The Home_State field is defined with SQLUPPER collation which is not case-sensitive. The list in these examples specifies New Hampshire as nH, rather than NH. The first example returns NH Home_State values, the second example does not return NH Home_State values:

The following example creates a cached query with a SIZE literal of 10. Specifying SIZE 10 is optimal for this query, because 10 corresponds in order-of-magnitude to the actual number of elements in the list. Changing the number of elements in the list does not create a separate cached query. Changing the SIZE literal does create a separate cached query:

The following example creates a cached query with a SIZE literal of 10. Specifying SIZE 10 is optimal for this query, because 10 corresponds in order-of-magnitude to the actual number of elements in the list. Changing the number of elements in the list does not create a separate cached query. Changing the SIZE literal does create a separate cached query: