> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cloud.cdata.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Data Virtuality Engine SQL Reference
> Additional SQL commands, syntax, and functions available when the Data Virtuality (DV) Engine is active for your account.
The features on this page are available only when the DV Engine is active for your account. Accounts on the QF driver do not have access to these features.
## EXPLAIN
Returns the execution plan for a SQL statement. EXPLAIN supports SELECT, DELETE, and other DML statements. The result set contains a single column, `plan`, of type string.
**Syntax**
```sql theme={null}
EXPLAIN { SELECT | INSERT | UPDATE | DELETE | ... }
```
**Examples**
```sql theme={null}
EXPLAIN SELECT * FROM Salesforce1.Salesforce.Account
```
## SQL Syntax
### CROSS APPLY / OUTER APPLY
A lateral join that applies a table-valued function or subquery to each row of the left table.
* `CROSS APPLY` Excludes left-side rows where the applied expression returns no rows (equivalent to an inner join).
* `OUTER APPLY` Keeps left-side rows even when the applied expression returns no rows, filling unmatched columns with NULL (equivalent to a left join).
**Syntax**
```sql theme={null}
FROM table { CROSS APPLY | OUTER APPLY } { tvf | subquery }
```
**Examples**
```sql theme={null}
SELECT t.Id, s.Value
FROM MyTable t
CROSS APPLY STRING_SPLIT(t.Tags, ',') AS s
```
## Window Functions
Window functions are evaluated in the DV Engine and are not pushed down to individual data sources.
### LEAD
Returns the value of `expr` from a row that is `offset` rows ahead of the current row in the window. Returns `default` when no such row exists.
**Syntax**
```sql theme={null}
LEAD(expr [, offset [, default]]) OVER (...)
```
**Parameters**
* **expr** The expression to evaluate in the target row.
* **offset** The number of rows ahead to look. The default value is 1.
* **default** The value to return when no row exists at the specified offset. The default value is NULL.
**Examples**
```sql theme={null}
SELECT Name, Salary,
LEAD(Salary, 1, 0) OVER (ORDER BY HireDate) AS NextSalary
FROM Employees
```
### LAG
Returns the value of `expr` from a row that is `offset` rows before the current row in the window. Returns `default` when no such row exists.
**Syntax**
```sql theme={null}
LAG(expr [, offset [, default]]) OVER (...)
```
**Parameters**
* **expr** The expression to evaluate in the target row.
* **offset** The number of rows behind to look. The default value is 1.
* **default** The value to return when no row exists at the specified offset. The default value is NULL.
**Examples**
```sql theme={null}
SELECT Name, Salary,
LAG(Salary) OVER (ORDER BY HireDate) AS PreviousSalary
FROM Employees
```
### FIRST\_VALUE
Returns the first value in the current window frame.
**Syntax**
```sql theme={null}
FIRST_VALUE(expr) OVER (...)
```
**Parameters**
* **expr** The expression to evaluate.
**Examples**
```sql theme={null}
SELECT Name, Salary,
FIRST_VALUE(Salary) OVER (PARTITION BY Department ORDER BY HireDate) AS FirstSalary
FROM Employees
```
### LAST\_VALUE
Returns the last value in the current window frame.
**Syntax**
```sql theme={null}
LAST_VALUE(expr) OVER (...)
```
**Parameters**
* **expr** The expression to evaluate.
**Examples**
```sql theme={null}
SELECT Name, Salary,
LAST_VALUE(Salary) OVER (
PARTITION BY Department ORDER BY HireDate
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LastSalary
FROM Employees
```
## Aggregate Functions
### STDDEV\_POP / STDDEV\_SAMP
Returns the population or sample standard deviation of a numeric column.
**Syntax**
```sql theme={null}
STDDEV_POP(expr)
STDDEV_SAMP(expr)
```
**Parameters**
* **expr** The numeric column or expression to evaluate.
**Examples**
```sql theme={null}
SELECT STDDEV_POP(Revenue) FROM Sales
SELECT STDDEV_SAMP(Revenue) FROM Sales
```
### VAR\_POP / VAR\_SAMP
Returns the population or sample variance of a numeric column.
**Syntax**
```sql theme={null}
VAR_POP(expr)
VAR_SAMP(expr)
```
**Parameters**
* **expr** The numeric column or expression to evaluate.
**Examples**
```sql theme={null}
SELECT VAR_POP(Revenue) FROM Sales
SELECT VAR_SAMP(Revenue) FROM Sales
```
### STRING\_AGG
Concatenates the values in a group into a single string, separated by the specified delimiter.
**Syntax**
```sql theme={null}
STRING_AGG(expr, delimiter)
```
**Parameters**
* **expr** The column or expression to concatenate.
* **delimiter** The separator string to place between concatenated values.
When `DISTINCT` or `ORDER BY` is used inside `STRING_AGG`, evaluation occurs in the DV Engine and is not pushed down to the source.
**Examples**
```sql theme={null}
SELECT STRING_AGG(Name, ', ') FROM Account GROUP BY Industry
```
### JSONARRAY\_AGG
Collects the values in a group into a JSON array. Returns a JSON-typed value.
**Syntax**
```sql theme={null}
JSONARRAY_AGG(expr)
```
**Parameters**
* **expr** The column or expression to collect.
**Examples**
```sql theme={null}
SELECT Id, JSONARRAY_AGG(Tag) FROM ArticleTags GROUP BY Id
```
## String Functions
### REGEXP\_REPLACE
Replaces substrings that match a regular expression pattern.
**Syntax**
```sql theme={null}
REGEXP_REPLACE(str, pattern, replacement [, flags])
```
**Parameters**
* **str** The input string.
* **pattern** The regular expression to match.
* **replacement** The string to substitute for each match.
* **flags** Optional modifier flags: `g` (replace all matches), `m` (multiline), `i` (case-insensitive).
**Examples**
```sql theme={null}
SELECT REGEXP_REPLACE(PhoneNumber, '[^0-9]', '', 'g') FROM Account
```
### REGEXP\_INSTR
Returns the 1-based start position of the first match of a regular expression in a string. Returns 0 if there is no match.
**Syntax**
```sql theme={null}
REGEXP_INSTR(str, pattern)
```
**Parameters**
* **str** The input string.
* **pattern** The regular expression to match.
**Examples**
```sql theme={null}
SELECT REGEXP_INSTR(Description, '[0-9]+') FROM Products
-- Result: position of the first digit sequence, or 0 if none
```
### REGEXP\_SUBSTR
Returns the substring matched by a regular expression. Returns NULL if there is no match.
**Syntax**
```sql theme={null}
REGEXP_SUBSTR(str, pattern)
```
**Parameters**
* **str** The input string.
* **pattern** The regular expression to match.
**Examples**
```sql theme={null}
SELECT REGEXP_SUBSTR(Description, '[0-9]+') FROM Products
-- Result: first digit sequence in Description, or NULL
```
### SPLIT\_PART
Returns the Nth field after splitting a string on a delimiter. Fields are 1-based.
**Syntax**
```sql theme={null}
SPLIT_PART(str, delimiter, n)
```
**Parameters**
* **str** The input string.
* **delimiter** The delimiter string.
* **n** The 1-based index of the field to return.
**Examples**
```sql theme={null}
SELECT SPLIT_PART('a,b,c', ',', 2)
-- Result: 'b'
```
### BASE64\_ENCODE / BASE64\_DECODE
Encodes binary data as a Base64 string, or decodes a Base64 string back to binary.
**Syntax**
```sql theme={null}
BASE64_ENCODE(bytes)
BASE64_DECODE(str)
```
**Parameters**
* **bytes** The binary data to encode.
* **str** The Base64-encoded string to decode.
**Examples**
```sql theme={null}
SELECT BASE64_ENCODE(FileContent) FROM Attachments
SELECT BASE64_DECODE(EncodedContent) FROM Attachments
```
### TEXT\_ENCODE / TEXT\_DECODE
Converts between a string and bytes using a named character set.
**Syntax**
```sql theme={null}
TEXT_ENCODE(str, charset)
TEXT_DECODE(bytes, charset)
```
**Parameters**
* **str** The string to encode.
* **bytes** The byte sequence to decode.
* **charset** The character set name, such as `UTF-8` or `ISO-8859-1`.
**Examples**
```sql theme={null}
SELECT TEXT_ENCODE(Notes, 'UTF-8') FROM Documents
SELECT TEXT_DECODE(RawBytes, 'ISO-8859-1') FROM Documents
```
### similarity
Returns a fuzzy string similarity score between 0.0 and 1.0.
**Syntax**
```sql theme={null}
similarity(v1, v2 [, algorithm])
```
**Parameters**
* **v1** The first string.
* **v2** The second string to compare with `v1`.
* **algorithm** Optional. The similarity algorithm to use. The default value is `jaro-winkler`. Alternatives include `levenshtein`.
**Examples**
```sql theme={null}
SELECT Name, similarity(Name, 'John Smith') AS Score
FROM Contacts
ORDER BY Score DESC
```
## Security and Crypto Functions
### SHA1 / SHA2\_256 / SHA2\_512
Computes a SHA hash of the input text and returns the result as a binary value. Use these functions as direct alternatives to `HASHBYTES` when you want to specify a fixed algorithm.
**Syntax**
```sql theme={null}
SHA1(text)
SHA2_256(text)
SHA2_512(text)
```
**Parameters**
* **text** The string to hash.
**Examples**
```sql theme={null}
SELECT SHA2_256(Password) FROM Users
```
### AES\_ENCRYPT / AES\_DECRYPT
Encrypts or decrypts a string using AES-256 symmetric encryption.
**Syntax**
```sql theme={null}
AES_ENCRYPT(text, key)
AES_DECRYPT(bytes, key)
```
**Parameters**
* **text** The plaintext string to encrypt.
* **bytes** The encrypted byte sequence to decrypt.
* **key** The AES-256 encryption key.
**Examples**
```sql theme={null}
SELECT AES_ENCRYPT(CreditCard, 'secret-key') FROM Orders
SELECT AES_DECRYPT(EncryptedCard, 'secret-key') FROM Orders
```
## JSON Functions
### JSONPATHVALUE
Extracts a scalar value from a JSON document using a JSONPath expression. This function is an alias for [`JSON_EXTRACT`](/en/SQL-Reference/String-Functions#json_extract) and is pushed down to CData drivers.
**Syntax**
```sql theme={null}
JSONPATHVALUE(json, path [, nullLeaf])
```
**Parameters**
* **json** The JSON document to query.
* **path** The JSONPath expression identifying the scalar value to extract.
* **nullLeaf** Optional Boolean. When `true`, a missing leaf node returns NULL instead of raising an error.
**Examples**
```sql theme={null}
SELECT JSONPATHVALUE(Payload, '$.customer.id') FROM Events
```
### JSONQUERY
Extracts a JSON fragment (an object or array) at the specified JSONPath. Returns a JSON-typed value, not a scalar.
**Syntax**
```sql theme={null}
JSONQUERY(json, path [, nullLeaf])
```
**Parameters**
* **json** The JSON document to query.
* **path** The JSONPath expression identifying the array or object to extract.
* **nullLeaf** Optional Boolean. When `true`, a missing leaf node returns NULL instead of raising an error.
**Examples**
```sql theme={null}
SELECT JSONQUERY(Payload, '$.items') FROM Orders
```
### JSONPARSE
Parses a string or CLOB value as JSON.
**Syntax**
```sql theme={null}
JSONPARSE(clob, wellformed)
```
**Parameters**
* **clob** The string or CLOB to parse.
* **wellformed** Boolean. When `true`, the function skips JSON validation.
**Examples**
```sql theme={null}
SELECT JSONPARSE(RawJson, true) FROM Logs
```
### JSONOBJECT
Constructs a JSON object from one or more name/value pairs.
**Syntax**
```sql theme={null}
JSONOBJECT(expr AS name [, expr AS name, ...])
```
**Parameters**
* **expr** The value expression for a key.
* **name** The key name for the value.
**Examples**
```sql theme={null}
SELECT JSONOBJECT(Name AS 'name', Revenue AS 'revenue') FROM Account
```
### JSONARRAY
Constructs a JSON array from a list of values.
**Syntax**
```sql theme={null}
JSONARRAY(expr [, expr, ...])
```
**Parameters**
* **expr** One or more values to include in the array.
**Examples**
```sql theme={null}
SELECT JSONARRAY(FirstName, LastName, Email) FROM Contacts
```
### JSONTOXML
Converts a JSON value to an XML document.
**Syntax**
```sql theme={null}
JSONTOXML(rootName, json)
```
**Parameters**
* **rootName** The name to use as the root XML element.
* **json** The JSON value to convert.
**Examples**
```sql theme={null}
SELECT JSONTOXML('root', Payload) FROM Events
```
### JSONTOARRAY
Extracts values from a JSON document using multiple JSONPath expressions and returns them as an array. Each colpath extracts one value from each element matched by the base path.
**Syntax**
```sql theme={null}
JSONTOARRAY(json, path, nullLeaf, colpaths...)
```
**Parameters**
* **json** The JSON document to query.
* **path** The base JSONPath for array iteration.
* **nullLeaf** Boolean. When `true`, missing leaf nodes return NULL instead of raising an error.
* **colpaths** One or more JSONPath expressions, each extracting a value from the current array element.
**Examples**
```sql theme={null}
SELECT JSONTOARRAY(Payload, '$.items[*]', true, '$.name', '$.qty') FROM Orders
```
### JSONPath Slice Syntax
The DV Engine extends standard JSONPath with array slice notation:
| Expression | Meaning |
| -------------- | -------------------------------------------------- |
| `$.items[..N]` | Elements from index 0 through N (inclusive) |
| `$.items[N..]` | Elements from index N through the end of the array |
**Examples**
```sql theme={null}
SELECT JSONQUERY(Payload, '$.items[..2]') FROM Orders
-- Returns a JSON array of the first three items (indices 0, 1, and 2)
SELECT JSONQUERY(Payload, '$.items[3..]') FROM Orders
-- Returns a JSON array of all items from index 3 onward
```
## Array Functions
### ARRAY\_GET
Returns the element at a 1-based index from an array.
**Syntax**
```sql theme={null}
ARRAY_GET(arr, idx)
```
**Parameters**
* **arr** The array to access.
* **idx** The 1-based index of the element to return.
**Examples**
```sql theme={null}
SELECT ARRAY_GET(Tags, 1) FROM Articles
```
### ARRAY\_LENGTH
Returns the number of elements in an array.
**Syntax**
```sql theme={null}
ARRAY_LENGTH(arr)
```
**Parameters**
* **arr** The array to measure.
**Examples**
```sql theme={null}
SELECT Name, ARRAY_LENGTH(Tags) FROM Articles
```
### ARRAY\_ADD
Returns a new array with a value appended to the end.
**Syntax**
```sql theme={null}
ARRAY_ADD(arr, value)
```
**Parameters**
* **arr** The source array.
* **value** The value to append.
**Examples**
```sql theme={null}
SELECT ARRAY_ADD(Tags, 'new-tag') FROM Articles
```
### ARRAY\_IN
Returns `true` if a value is an element of the array.
**Syntax**
```sql theme={null}
ARRAY_IN(haystack, needle)
```
**Parameters**
* **haystack** The array to search.
* **needle** The value to look for.
**Examples**
```sql theme={null}
SELECT * FROM Articles WHERE ARRAY_IN(Tags, 'featured')
```
### ARRAY\_LIKE
Returns `true` if any element in the array matches a LIKE pattern.
**Syntax**
```sql theme={null}
ARRAY_LIKE(haystack, pattern)
```
**Parameters**
* **haystack** The array to search.
* **pattern** The LIKE pattern to match against each element.
**Examples**
```sql theme={null}
SELECT * FROM Articles WHERE ARRAY_LIKE(Tags, '%tech%')
```
### ARRAY\_LIKE\_REGEX
Returns `true` if any element in the array matches a regular expression pattern.
**Syntax**
```sql theme={null}
ARRAY_LIKE_REGEX(haystack, pattern)
```
**Parameters**
* **haystack** The array to search.
* **pattern** The regular expression to match against each element.
**Examples**
```sql theme={null}
SELECT * FROM Articles WHERE ARRAY_LIKE_REGEX(Tags, '^tech.*')
```
### ASARRAY / ASLIST
Constructs an array or list from individual values.
**ASLIST**-Returns arguments as a list: returns an Object (java.util.List).\
**ASARRAY**-Returns arguments as an array: returns a SQL array.
Use ASARRAY when a SQL array type is needed. Use ASLIST when a generic list is expected; for example, as input to other functions that accept a list.
**Syntax**
```sql theme={null}
ASARRAY(v1, v2, ...)
ASLIST(v1, v2, ...)
```
**Parameters**
* **v1, v2, ...** The values to include in the array or list.
**Examples**
```sql theme={null}
SELECT ASARRAY(FirstName, LastName) FROM Contacts
```
## Numeric Functions
### BITAND / BITOR / BITXOR
Performs a bitwise AND, OR, or XOR operation on two integers.
**Syntax**
```sql theme={null}
BITAND(x, y)
BITOR(x, y)
BITXOR(x, y)
```
**Parameters**
* **x** The first integer.
* **y** The second integer.
**Examples**
```sql theme={null}
SELECT BITAND(Flags, 3) FROM Permissions
SELECT BITOR(Flags, 4) FROM Permissions
SELECT BITXOR(Flags, 7) FROM Permissions
```
### BITNOT
Performs a bitwise NOT operation on an integer.
**Syntax**
```sql theme={null}
BITNOT(x)
```
**Parameters**
* **x** The integer to negate bitwise.
**Examples**
```sql theme={null}
SELECT BITNOT(Flags) FROM Permissions
```
## Date and Time Functions
### DATE\_FORMAT
Formats a date or timestamp as a string using a MySQL-style format pattern.
**Syntax**
```sql theme={null}
DATE_FORMAT(date, format)
```
**Parameters**
* **date** The date or timestamp value to format.
* **format** The format pattern string (for example, `'%Y-%m-%d'` or `'%Y-%m-%d %H:%i:%s'`).
**Examples**
```sql theme={null}
SELECT DATE_FORMAT(CreatedDate, '%Y-%m-%d') FROM Account
-- Result: '2024-01-15'
```
### ISOWEEK / ISOYEAR
Returns the ISO 8601 week number or ISO year for a given date. Week 1 is the week that contains the first Thursday of the year.
**Syntax**
```sql theme={null}
ISOWEEK(date)
ISOYEAR(date)
```
**Parameters**
* **date** The date value to evaluate.
**Examples**
```sql theme={null}
SELECT CreatedDate, ISOWEEK(CreatedDate), ISOYEAR(CreatedDate) FROM Account
```
### Date-Range Functions
The DV Engine provides convenience functions that return date-range boundary values. Use these in `WHERE` clauses with `BETWEEN` or comparison operators to filter by relative time periods.
#### L\_LAST\_MONTH / L\_THIS\_MONTH / L\_NEXT\_MONTH
Returns the start boundary of the previous, current, or next calendar month.
```sql theme={null}
WHERE CreatedDate BETWEEN L_LAST_MONTH() AND L_THIS_MONTH()
```
#### L\_LAST\_N\_MONTHS(n) / L\_NEXT\_N\_MONTHS(n)
Returns the start boundary of a rolling window of N calendar months backward or forward from today.
```sql theme={null}
WHERE CreatedDate >= L_LAST_N_MONTHS(3)
```
#### L\_LAST\_QUARTER / L\_THIS\_QUARTER / L\_NEXT\_QUARTER
Returns the start boundary of the previous, current, or next calendar quarter.
```sql theme={null}
WHERE CloseDate BETWEEN L_LAST_QUARTER() AND L_THIS_QUARTER()
```
#### L\_LAST\_N\_QUARTERS(n) / L\_NEXT\_N\_QUARTERS(n)
Returns the start boundary of a rolling window of N quarters backward or forward from today.
```sql theme={null}
WHERE CloseDate >= L_LAST_N_QUARTERS(2)
```
#### L\_LAST\_YEAR / L\_THIS\_YEAR / L\_NEXT\_YEAR
Returns the start boundary of the previous, current, or next calendar year.
```sql theme={null}
WHERE CreatedDate BETWEEN L_LAST_YEAR() AND L_THIS_YEAR()
```
#### L\_LAST\_N\_YEARS(n) / L\_NEXT\_N\_YEARS(n)
Returns the start boundary of a rolling window of N years backward or forward from today.
```sql theme={null}
WHERE CreatedDate >= L_LAST_N_YEARS(3)
```
#### L\_LAST\_90\_DAYS / L\_NEXT\_90\_DAYS
Returns the boundary date for a rolling 90-day window backward or forward from today.
```sql theme={null}
WHERE CreatedDate BETWEEN L_LAST_90_DAYS() AND CURRENT_DATE()
```
## Miscellaneous Functions
### UUID
Generates a new random UUID string.
**Syntax**
```sql theme={null}
UUID()
```
**Examples**
```sql theme={null}
SELECT UUID()
-- Result: 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'
```
## Optimizer Hints
Optimizer hints are embedded in SQL comments and instruct the DV Engine on how to plan query execution. They do not change query results.
### MAKEDEP
Forces a dependent join: the engine evaluates this table first and uses its key values as a `WHERE key IN (...)` filter when querying the other source. This reduces the number of rows fetched from large sources.
**Syntax**
```sql theme={null}
FROM table1 JOIN /*+ MAKEDEP */ table2 ON join_criteria
Supports optional sub-options:
FROM table1 JOIN /*+ MAKEDEP(MAX:1000) */ table2 ON join_criteria
FROM table1 JOIN /*+ MAKEDEP(JOIN) */ table2 ON join_criteria
```
**Example**
```sql theme={null}
SELECT *
FROM LargeTable
JOIN /*+ MAKEDEP */ SmallTable ON LargeTable.SmallTableId = SmallTableId
```
### MAKENOTDEP
Prevents the optimizer from using a dependent join for this table.
**Syntax**
```sql theme={null}
FROM table1 JOIN /*+ MAKENOTDEP */ table2 ON join_criteria
```
**Examples**
```sql theme={null}
SELECT *
FROM LargeTable
JOIN /*+ MAKENOTDEP */ SmallTable ON LargeTable.SmallTableId = SmallTableId
```
### MAKEIND
Forces a table to be the independent (driving) side of a dependent join. The engine evaluates this table first and uses its key values to filter the other side.
**Syntax**
```sql theme={null}
FROM /*+ MAKEIND */ table1 JOIN table2 ON join_criteria
```
**Examples**
In the following example, SmallTable drives the join so LargeTable gets filtered by an IN list.
```sql theme={null}
SELECT *
FROM /*+ MAKEIND */ SmallTable
JOIN LargeTable ON SmallTable.Id = LargeTable.SmallTableId
```
### Source Hint
Passes a hint string verbatim to the source connector's SQL translator.
**Syntax**
```sql theme={null}
/*+ sh hint_text */
```
**Examples**
```sql theme={null}
SELECT /*+ sh NOLOCK */ * FROM Account
```