%INLIST (SQL)
Synopsis
scalar-expression %INLIST list [SIZE ((nn))]
Arguments
Argument | Description |
---|---|
scalar-expression | A scalar expression (most commonly a data column) whose values are being compared with list elements. |
list | A %List structure containing one or more elements. |
SIZE ((nn)) | 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.
%INLIST performs an equality comparison with each of the elements of list. %INLIST comparisons use the collation type defined for the scalar-expression. Therefore, comparisons of list elements may be case-sensitive or not case-sensitive, depending on the collation of scalar-expression. By default, string data type fields are defined with SQLUPPER collation, which is not case-sensitive. You can define the string collation default for the current namespace and specify a non-default field collation type when defining a field/property.
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.
%INLIST can be used wherever a predicate condition can be specified, as described in Overview of Predicates.
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.
A WHERE clause with the format WHERE ? %INLIST ListOfString, where ? is a literal value and ListOfString is the name of a column in the table that stores a %List of strings, is not sargable. That is, such a query does not use an index defined on ListOfString. Instead, to improve the efficiency of such a search, use FOR SOME %ELEMENT, which is sargable.
%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:
SET bday=$LISTBUILD(50039)
FOR i=50039:1:50403 {SET bday=bday_$LISTBUILD(i) }
SET q1 = "SELECT Name,DOB FROM Sample.Person "
SET q2 = "WHERE DOB %INLIST ?"
SET myquery = q1_q2
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute(bday)
DO rset.%Display()
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:
SET births=$LISTBUILD("1978-01-15","1978-08-22","1978-10-01")
SET q1 = "SELECT Name,DOB FROM Sample.Person "
SET q2 = "WHERE %ODBCOUT(DOB) %INLIST ?"
SET myquery = q1_q2
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute(births)
DO rset.%Display()
%INLIST and IN
Both the %INLIST and IN predicates can be used to supply multiple values to use for equality comparisons. The following examples return the same results:
SELECT Name, Home_State
FROM Sample.Person
WHERE Home_State %INLIST $LISTBUILD('VT','NH','ME')
SELECT Name,Home_State
FROM Sample.Person
WHERE Home_State IN('VT','NH','ME')
For 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 Cached Queries.
Examples
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:
SELECT Name,Home_State
FROM Sample.Person
WHERE Home_State %INLIST $LISTBUILD("VT","nH","ME")
SELECT Name,Home_State
FROM Sample.Person
WHERE %EXACT(Home_State) %INLIST $LISTBUILD("VT","nH","ME")
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:
SELECT Name,Home_State
FROM Sample.Person
WHERE Home_State %INLIST $LISTBUILD("VT","NH","ME") SIZE ((10))
See Also
-
$LISTBUILD function
-
IN predicate