[3] DOPE functionality

Supported Expressions and Clauses

Clauses

SELECT Clause

Syntax	DOPE	Link
SELECT	QueryBuilder().select(...)	SELECT Clause
SELECT *	QueryBuilder().selectAsterisk()	SELECT Syntax
SELECT DISTINCT	QueryBuilder().selectDistinct(...)	SELECT Clause
SELECT RAW	QueryBuilder().selectRaw(...)	SELECT Clause
SELECT * FROM	QueryBuilder().selectFrom(...)	SELECT Syntax
FROM	.from(...)	FROM Clause
UNNEST	.unnest(...)	UNNEST Clause
JOIN ... ON	.join(...)	JOIN Clause
JOIN ... ON KEYS	.join(...)	JOIN Clause
JOIN ... ON KEY FOR	.join(...)	JOIN Clause
INNER JOIN ... ON	.innerJoin(...)	JOIN Clause
INNER JOIN ... ON KEYS	.innerJoin(...)	JOIN Clause
INNER JOIN ... ON KEY FOR	.innerJoin(...)	JOIN Clause
LEFT JOIN ... ON	.leftJoin(...)	JOIN Clause
LEFT JOIN ... ON KEYS	.leftJoin(...)	JOIN Clause
LEFT JOIN ... ON KEY FOR	.leftJoin(...)	JOIN Clause
RIGHT JOIN ... ON	.rightJoin(...)	JOIN Clause
RIGHT JOIN ... ON KEYS	.rightJoin(...)	JOIN Clause
RIGHT JOIN ... ON KEY FOR	.rightJoin(...)	JOIN Clause
WHERE	.where(...)	WHERE Clause
GROUP BY	.groupBy(...)	GROUP BY Clause
ORDER BY	.orderBy(...)	ORDER BY Clause
LIMIT	.limit(...)	LIMIT Clause
OFFSET	.offset(...)	OFFSET Clause
(SELECT ...)	.asExpression()	Subqueries
(SELECT ...) AS ...	.alias(...)	Aliases
UNION	.union(...)	Set Operations
UNION ALL	.unionAll(...)	Set Operations
INTERSECT	.intersect(...)	Set Operations
INTERSECT ALL	.intersectAll(...)	Set Operations
EXCEPT	.except(...)	Set Operations
EXCEPT ALL	.exceptAll(...)	Set Operations

---

UPDATE Clause

Syntax	DOPE	Link
UPDATE	QueryBuilder().update(...)	UPDATE Statement
SET	.set(...)	SET Clause
UNSET	.unset(...)	UNSET Clause
WHERE	.where(...)	WHERE Clause
LIMIT	.limit(...)	LIMIT Clause
RETURNING	.returning(...)	RETURNING Clause

---

DELETE Clause

Syntax	DOPE	Link
DELETE	QueryBuilder().deleteFrom(...)	DELETE Statement
WHERE	.where(...)	WHERE Clause
LIMIT	.limit(...)	LIMIT Clause
OFFSET	.offset(...)	OFFSET Clause
RETURNING	.returning(...)	RETURNING Clause

---

Supported Functions

Number Functions

Syntax	DOPE	Link
ABS	abs(...)	ABS Function
ACOS	acos(...)	ACOS Function
ASIN	asin(...)	ASIN Function
ATAN	atan(...)	ATAN Function
ATAN2	atan2(...)	ATAN2 Function
CEIL	ceil(...)	CEIL Function
COS	cos(...)	COS Function
DEGREES	degrees(...)	DEGREES Function
E	e()	E Constant
EXP	exp(...)	EXP Function
FLOOR	floor(...)	FLOOR Function
LOG	log(...)	LOG Function
LN	ln(...)	LN Function
POWER	power(...)	POWER Function
PI	pi()	PI Constant
RADIANS	radians(...)	RADIANS Function
RANDOM	random(...)	RANDOM Function
ROUND	round(...)	ROUND Function
SIGN	sign(...)	SIGN Function
SIN	sin(...)	SIN Function
SQRT	sqrt(...)	SQRT Function
TAN	tan(...)	TAN Function
TRUNC	trunc(...)	TRUNC Function

---

String Functions

Syntax	DOPE	Link
CONCAT2	concat2(...)	CONCAT2 Function
CONCAT	concat(...)	CONCAT Function
CONTAINS	contains(...)	CONTAINS Function
INITCAP	initCap(...)	INITCAP Function
LENGTH	length(...)	LENGTH Function
LOWER	lower(...)	LOWER Function
LPAD	lpad(...)	LPAD Function
LTRIM	ltrim(...)	LTRIM Function
MASK	mask(...)	MASK Function
MB_LENGTH	mbLength(...)	MB_LENGTH Function
MB_LPAD	mbLpad(...)	MB_LPAD Function
MB_POSITION1	mbPosition1(...)	MB_POSITION1 Function
MB_POSITION	mbPosition(...)	MB_POSITION Function
MB_RPAD	mbRpad(...)	MB_RPAD Function
MB_SUBSTR1	mbSubstring1(...)	MB_SUBSTR1 Function
MB_SUBSTR	mbSubstring(...)	MB_SUBSTR Function
POSITION1	position1(...)	POSITION1 Function
POSITION	position(...)	POSITION Function
REPEAT	repeat(...)	REPEAT Function
REPLACE	replace(...)	REPLACE Function
REVERSE	reverse(...)	REVERSE Function
RPAD	rpad(...)	RPAD Function
RTRIM	rtrim(...)	RTRIM Function
SPLIT	split(...)	SPLIT Function
SUBSTR1	substring1(...)	SUBSTR1 Function
SUBSTR	substring(...)	SUBSTR Function
SUFFIXES	suffixes(...)	SUFFIXES Function
TITLE	title(...)	TITLE Function
TOKENS	tokens(...)	TOKENS Function
TRIM	trim(...)	TRIM Function
UPPER	upper(...)	UPPER Function
URL_DECODE	urlDecode(...)	URL_DECODE Function
URL_ENCODE	urlEncode(...)	URL_ENCODE Function

---

Date Functions

Syntax	DOPE	Link
NOW_STR	nowString(...)	NOW_STR Function

---

Array Functions

Syntax	DOPE	Link
ARRAY_APPEND	arrayAppend(...)	ARRAY_APPEND Function
ARRAY_AVG	arrayAverage(...)	ARRAY_AVG Function
ARRAY_BINARY_SEARCH	arrayBinarySearch(...)	ARRAY_BINARY_SEARCH Function
ARRAY_CONCAT	arrayConcat(...)	ARRAY_CONCAT Function
ARRAY_CONTAINS	arrayContains(...)	ARRAY_CONTAINS Function
ARRAY_COUNT	arrayCount(...)	ARRAY_COUNT Function
ARRAY_DISTINCT	arrayDistinct(...)	ARRAY_DISTINCT Function
ARRAY_EXCEPT	arrayExcept(...)	ARRAY_EXCEPT Function
ARRAY_FLATTEN	arrayFlatten(...)	ARRAY_FLATTEN Function
ARRAY_IFNULL	arrayIfNull(...)	ARRAY_IFNULL Function
ARRAY_INSERT	arrayInsert(...)	ARRAY_INSERT Function
ARRAY_INTERSECT	arrayIntersect(...)	ARRAY_INTERSECT Function
ARRAY_LENGTH	arrayLength(...)	ARRAY_LENGTH Function
ARRAY_MAX	arrayMax(...)	ARRAY_MAX Function
ARRAY_MIN	arrayMin(...)	ARRAY_MIN Function
ARRAY_MOVE	arrayMove(...)	ARRAY_MOVE Function
ARRAY_POSITION	arrayPosition(...)	ARRAY_POSITION Function
ARRAY_PREPEND	arrayPrepend(...)	ARRAY_PREPEND Function
ARRAY_PUT	arrayPut(...)	ARRAY_PUT Function
ARRAY_RANGE	arrayRange(...)	ARRAY_RANGE Function
ARRAY_REMOVE	arrayRemove(...)	ARRAY_REMOVE Function
ARRAY_REPEAT	arrayRepeat(...)	ARRAY_REPEAT Function
ARRAY_REPLACE	arrayReplace(...)	ARRAY_REPLACE Function
ARRAY_REVERSE	arrayReverse(...)	ARRAY_REVERSE Function
ARRAY_SORT	arraySort(...)	ARRAY_SORT Function
ARRAY_STAR	arrayStar(...)	ARRAY_STAR Function
ARRAY_SUM	arraySum(...)	ARRAY_SUM Function
ARRAY_SYMDIFF	arraySymDiff(...)	ARRAY_SYMDIFF Function
ARRAY_UNION	arrayUnion(...)	ARRAY_UNION Function

---

Collection Functions

Syntax	DOPE	Link
EXISTS	exists(...)	EXISTS Function
IN	.inArray(...)	IN Function
NOT IN	.notInArray(...)	NOT IN Function
ANY SATISFIES	.any(...)	ANY Function
EVERY SATISFIES	.every(...)	EVERY Function
WITHIN	.withinArray(...)	WITHIN Function
NOT WITHIN	.notWithinArray(...)	NOT WITHIN Function

---

Type Functions

Syntax	DOPE	Link
ISARRAY	.isArray()	ISARRAY Function
ISATOM	.isAtom()	ISATOM Function
ISBOOLEAN	.isBoolean()	ISBOOLEAN Function
ISNUMBER	.isNumber()	ISNUMBER Function
ISOBJECT	.isObject()	ISOBJECT Function
ISSTRING	.isString()	ISSTRING Function
TOARRAY	.toArray()	TOARRAY Function
TOBOOLEAN	.toBool()	TOBOOLEAN Function
TONUMBER	.toNumber()	TONUMBER Function
TOOBJECT	.toObject()	TOOBJECT Function
TOSTRING	.toStr()	TOSTRING Function
TYPE	typeOf(...)	TYPE Function

---

Comparison Functions

Syntax	DOPE	Link
GREATEST	.greatestOf()	GREATEST Function
LEAST	.leastOf()	LEAST Function

---

Operators and Expressions

Logical Operators

Syntax	DOPE	Link
AND	.and(...)	AND Operator
OR	.or(...)	OR Operator
NOT	not(...)	NOT Operator

---

Aggregate Functions

Syntax	DOPE	Link
ARRAY_AGG	arrayAggregate(...)	ARRAY_AGG Function
AVG	avg(...)	AVG Function
COUNT	count(...)	COUNT Function
COUNT(*)	countAsterisk()	COUNT(*) Function
MAX	max(...)	MAX Function
MEAN	mean(...)	MEAN Function
MEDIAN	median(...)	MEDIAN Function
MIN	min(...)	MIN Function
STDDEV	stdDev(...)	STDDEV Function
SUM	sum(...)	SUM Function
VARIANCE	variance(...)	VARIANCE Function

---

Arithmetic Operators

Syntax	DOPE	Link
+	.add(...)	Addition Operator
/	.div(...)	Division Operator
%	.mod(...)	Modulo Operator
*	.mul(...)	Multiplication Operator
-	.sub(...)	Subtraction Operator
-(...)	neg(...)	Negation Operator

---

Relational Operators

Syntax	DOPE	Link
=	.isEqualTo(...)	Equality Operator
!=	.isNotEqualTo(...)	Inequality Operator
>=	.isGreaterOrEqualThan(...)	Greater Than or Equal Operator
>	.isGreaterThan(...)	Greater Than Operator
<=	.isLessOrEqualThan(...)	Less Than or Equal Operator
<	.isLessThan(...)	Less Than Operator
BETWEEN	.between(...)	BETWEEN Operator
NOT BETWEEN	.notBetween(...)	NOT BETWEEN Operator
IS MISSING	.isMissing()	IS MISSING Operator
IS NOT MISSING	.isNotMissing()	IS NOT MISSING Operator
IS NULL	.isNull()	IS NULL Operator
IS NOT NULL	.isNotNull()	IS NOT NULL Operator
IS VALUED	.isValued()	IS VALUED Operator
IS NOT VALUED	.isNotValued()	IS NOT VALUED Operator
LIKE	.isLike(...)	LIKE Operator
NOT LIKE	.isNotLike(...)	LIKE Operator

---

Conditional Functions for Unknowns

Syntax	DOPE	Link
DECODE	decode(...)	DECODE Function
IFMISSING	ifMissing(...)	IFMISSING Function
IFMISSINGORNULL	ifMissingOrNull(...)	IFMISSINGORNULL Function
COALESCE	coalesce(...)	COALESCE Function
IFNULL	ifNull(...)	IFNULL Function
NVL2	nvl2(...)	NVL2 Function
NVL	nvl(...)	NVL Function

---

Meta Functions and Operators

Syntax	DOPE	Link
META()	meta()	META Function
META(...)	meta(...)	META Function
META(...).cas	meta(...).cas	META.cas Property
META(...).expiration	meta(...).expiration	META.expiration Property
META(...).flags	meta(...).flags	META.flags Property
META(...).id	meta(...).id	META.id Property
META(...).type	meta(...).type	META.type Property
META(...).keyspace	meta(...).keyspace	META.keyspace Property

---

Conditional Operators

Syntax	DOPE	Link
CASE	case()	CASE Expression
CASE ...	case(...)	CASE Expression
WHEN THEN	.condition(...)	CASE WHEN THEN
ELSE	.otherwise(...)	CASE ELSE

---

Use Clause

Syntax	DOPE	Link
USE INDEX	.useIndex(...)	USE INDEX Clause
USING GSI	.useGsiIndex(...)	USING GSI Clause
USING FTS	.useFtsIndex(...)	USING FTS Clause
USE KEYS	.useKeys(...)	USE KEYS Clause