The IN and NOT IN operators cause the values appearing in the WHERE clause predicate to be tested for membership in a collection of values, which can be contained in a specified file, represented as a value list, or the result of a noncorrelated subquery.

The syntax for the IN predicate is as follows:

The set of values contained in the

value list

must be compatible with the datatype of

x

(which is typically a column name). The NULL keyword cannot be included in the value list (for comparisons against null, use a NULL predicate instead). If a SELECT statement (subquery) is used with the IN predicate, the subquery may only return values from a single column. The individual values contained in the value list must be separated by commas.

When the alternative IN-file option is used, the parameters following the "IN" keyword must all be contained within a set of braces

{ }

. The one required element of the IN-file option is the path of a single file containing the list of values in the test set, enclosed by single quotation marks and preceded by the keyword "SOURCEFILE". If the specified file is missing, an error is returned and the whole query is cancelled.

One of the two optional elements of the IN-file clause is the delimiter character, which is a single ASCII character that will act as the list delimiter in the specified text file. This character must be contained in single quotation marks and preceded by the keyword "DELIMITER". By default, if the delimiter is not specified, it is assumed to be a newline (or carriage return plus newline) character:

\n

or

\r\n

. Hexadecimal characters are not supported at this time.

The other optional element in the IN-file clause is the keyword "REMOTE", which (when present) indicates that the specified file is on the Data Vault Service side. If this keyword is omitted, the file is assumed to be on the

client

side: that is, the machine from where the query originated.

Note that multiple files cannot be specified in a single IN-file clause. However, multiple IN-file clauses, each referencing a different file, can be included in the same query.

Also note that each file name specified by the IN-file clauses in the same query must be unique, even if they are in different locations. This is because all of the files will be copied to the same SHAREDIR folder for processing, and so the names must not conflict.