Logical Operators

When querying documents or Vault objects, you can use the logical operators in the WHERE clause and FIND search string.

AND

Use the AND operator to retrieve results that meet two or more conditions. The following query returns Approved documents of the Reference Document type:

SELECT id, name__v
FROM documents
WHERE TONAME(type__v) = 'reference_document__c' AND TONAME(status__v) = 'approved__v'

BETWEEN

Use BETWEEN operator with AND to compare data between two different values. The following query returns the documents created between the dates of ‘2018-11-01’ and '2018-12-01’.

SELECT id, name__v
FROM documents
WHERE document_creation_date__v BETWEEN '2018-11-01' AND '2018-12-01'

CONTAINS

Use the CONTAINS operator to enclose multiple values in parentheses. This uses the OR operator logic. The following query returns documents with English OR Spanish OR French set on the language field.

SELECT id, name__v
FROM documents
WHERE TONAME(language__v)
CONTAINS ('english__c', 'spanish__c', 'french__c')

The CONTAINS operator does not support workflows (legacy) queries.

IN

Use the IN operator to test whether a field value (placed before the IN operator) is in the list of values provided after the IN operator.

The following query returns the id for all products referenced by a document.

SELECT id
FROM product__v
WHERE id
IN (SELECT id FROM document_product__vr)

The IN operator can only be used for inner join relationship queries on documents and objects.

The WHERE IN subquery uses the relationship name to identify the connection between objects. It then qualifies the primary records based on the existence of related records (and any additional criteria defined within the subquery).

LIKE

Use the LIKE operator with the wildcard character % to search for matching field values when you don’t know the entire value.

The following query returns documents where the name__v value starts with N.

SELECT id, name__v
FROM documents
WHERE name__v LIKE 'N%'

SELECT statements do not support LIKE '%string' (with a leading wildcard). For example, LIKE '%DOC will return an error response.

SHOW statements do support the leading wildcard on LIKE. For example, LIKE '%__v' will find names ending in __v.

NOT

Use the NOT operator to negate an entire search string in the FIND clause. This operator is not supported on other clauses.

The following query returns documents that do not contain the word cholecap (case insensitive):

SELECT id, name__v
FROM documents
FIND (NOT 'cholecap' )

OR

Use the OR operator to retrieve results that meet any of two or more conditions. Note that VQL does not support the OR operator between different query objects in a WHERE clause.

The following query includes documents with a version creation date or modified date after midnight on April 23, 2018:

SELECT id, name__v
FROM documents
WHERE version_creation_date__v > '2018-04-23' OR version_modified_date__v > '2018-04-23'