Data Virtuality Engine SQL Reference

EXPLAIN { SELECT | INSERT | UPDATE | DELETE | ... }

EXPLAIN SELECT * FROM Salesforce1.Salesforce.Account

FROM table { CROSS APPLY | OUTER APPLY } { tvf | subquery }

SELECT t.Id, s.Value
FROM MyTable t
CROSS APPLY STRING_SPLIT(t.Tags, ',') AS s

LEAD(expr [, offset [, default]]) OVER (...)

SELECT Name, Salary,
       LEAD(Salary, 1, 0) OVER (ORDER BY HireDate) AS NextSalary
FROM Employees

LAG(expr [, offset [, default]]) OVER (...)

SELECT Name, Salary,
       LAG(Salary) OVER (ORDER BY HireDate) AS PreviousSalary
FROM Employees

FIRST_VALUE(expr) OVER (...)

SELECT Name, Salary,
       FIRST_VALUE(Salary) OVER (PARTITION BY Department ORDER BY HireDate) AS FirstSalary
FROM Employees

LAST_VALUE(expr) OVER (...)

SELECT Name, Salary,
       LAST_VALUE(Salary) OVER (
           PARTITION BY Department ORDER BY HireDate
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS LastSalary
FROM Employees

STDDEV_POP(expr)
STDDEV_SAMP(expr)

SELECT STDDEV_POP(Revenue) FROM Sales
SELECT STDDEV_SAMP(Revenue) FROM Sales

VAR_POP(expr)
VAR_SAMP(expr)

SELECT VAR_POP(Revenue) FROM Sales
SELECT VAR_SAMP(Revenue) FROM Sales

STRING_AGG(expr, delimiter)

SELECT STRING_AGG(Name, ', ') FROM Account GROUP BY Industry

JSONARRAY_AGG(expr)

SELECT Id, JSONARRAY_AGG(Tag) FROM ArticleTags GROUP BY Id

REGEXP_REPLACE(str, pattern, replacement [, flags])

SELECT REGEXP_REPLACE(PhoneNumber, '[^0-9]', '', 'g') FROM Account

REGEXP_INSTR(str, pattern)

SELECT REGEXP_INSTR(Description, '[0-9]+') FROM Products
-- Result: position of the first digit sequence, or 0 if none

REGEXP_SUBSTR(str, pattern)

SELECT REGEXP_SUBSTR(Description, '[0-9]+') FROM Products
-- Result: first digit sequence in Description, or NULL

SPLIT_PART(str, delimiter, n)

SELECT SPLIT_PART('a,b,c', ',', 2)
-- Result: 'b'

BASE64_ENCODE(bytes)
BASE64_DECODE(str)

SELECT BASE64_ENCODE(FileContent) FROM Attachments
SELECT BASE64_DECODE(EncodedContent) FROM Attachments

TEXT_ENCODE(str, charset)
TEXT_DECODE(bytes, charset)

SELECT TEXT_ENCODE(Notes, 'UTF-8') FROM Documents
SELECT TEXT_DECODE(RawBytes, 'ISO-8859-1') FROM Documents

similarity(v1, v2 [, algorithm])

SELECT Name, similarity(Name, 'John Smith') AS Score
FROM Contacts
ORDER BY Score DESC

SHA1(text)
SHA2_256(text)
SHA2_512(text)

SELECT SHA2_256(Password) FROM Users

AES_ENCRYPT(text, key)
AES_DECRYPT(bytes, key)

SELECT AES_ENCRYPT(CreditCard, 'secret-key') FROM Orders
SELECT AES_DECRYPT(EncryptedCard, 'secret-key') FROM Orders

JSONPATHVALUE(json, path [, nullLeaf])

SELECT JSONPATHVALUE(Payload, '$.customer.id') FROM Events

JSONQUERY(json, path [, nullLeaf])

SELECT JSONQUERY(Payload, '$.items') FROM Orders

JSONPARSE(clob, wellformed)

SELECT JSONPARSE(RawJson, true) FROM Logs

JSONOBJECT(expr AS name [, expr AS name, ...])

SELECT JSONOBJECT(Name AS 'name', Revenue AS 'revenue') FROM Account

JSONARRAY(expr [, expr, ...])

SELECT JSONARRAY(FirstName, LastName, Email) FROM Contacts

JSONTOXML(rootName, json)

SELECT JSONTOXML('root', Payload) FROM Events

JSONTOARRAY(json, path, nullLeaf, colpaths...)

SELECT JSONTOARRAY(Payload, '$.items[*]', true, '$.name', '$.qty') FROM Orders

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_GET(arr, idx)

SELECT ARRAY_GET(Tags, 1) FROM Articles

ARRAY_LENGTH(arr)

SELECT Name, ARRAY_LENGTH(Tags) FROM Articles

ARRAY_ADD(arr, value)

SELECT ARRAY_ADD(Tags, 'new-tag') FROM Articles

ARRAY_IN(haystack, needle)

SELECT * FROM Articles WHERE ARRAY_IN(Tags, 'featured')

ARRAY_LIKE(haystack, pattern)

SELECT * FROM Articles WHERE ARRAY_LIKE(Tags, '%tech%')

ARRAY_LIKE_REGEX(haystack, pattern)

SELECT * FROM Articles WHERE ARRAY_LIKE_REGEX(Tags, '^tech.*')

ASARRAY(v1, v2, ...)
ASLIST(v1, v2, ...)

SELECT ASARRAY(FirstName, LastName) FROM Contacts

BITAND(x, y)
BITOR(x, y)
BITXOR(x, y)

SELECT BITAND(Flags, 3) FROM Permissions
SELECT BITOR(Flags, 4) FROM Permissions
SELECT BITXOR(Flags, 7) FROM Permissions

BITNOT(x)

SELECT BITNOT(Flags) FROM Permissions

DATE_FORMAT(date, format)

SELECT DATE_FORMAT(CreatedDate, '%Y-%m-%d') FROM Account
-- Result: '2024-01-15'

ISOWEEK(date)
ISOYEAR(date)

SELECT CreatedDate, ISOWEEK(CreatedDate), ISOYEAR(CreatedDate) FROM Account

WHERE CreatedDate BETWEEN L_LAST_MONTH() AND L_THIS_MONTH()

WHERE CreatedDate >= L_LAST_N_MONTHS(3)

WHERE CloseDate BETWEEN L_LAST_QUARTER() AND L_THIS_QUARTER()

WHERE CloseDate >= L_LAST_N_QUARTERS(2)

WHERE CreatedDate BETWEEN L_LAST_YEAR() AND L_THIS_YEAR()

WHERE CreatedDate >= L_LAST_N_YEARS(3)

WHERE CreatedDate BETWEEN L_LAST_90_DAYS() AND CURRENT_DATE()

UUID()

SELECT UUID()
-- Result: 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'

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

SELECT *
FROM LargeTable 
JOIN /*+ MAKEDEP */ SmallTable ON LargeTable.SmallTableId = SmallTableId

FROM table1 JOIN /*+ MAKENOTDEP */ table2 ON join_criteria

SELECT *
FROM LargeTable 
JOIN /*+ MAKENOTDEP */ SmallTable ON LargeTable.SmallTableId = SmallTableId

FROM /*+ MAKEIND */ table1 JOIN table2 ON join_criteria

SELECT *
FROM /*+ MAKEIND */ SmallTable
JOIN LargeTable ON SmallTable.Id = LargeTable.SmallTableId

/*+ sh hint_text */

SELECT /*+ sh NOLOCK */ * FROM Account