The first parameter to the QUERY function is an SQL query. A subset of the ANSI SQL SELECT statement is supported with one important variation - instead of table names (for example, in the FROM clause), a question mark parameter marker is used. An import function should then be used to provide the data for that virtual "table" in the query.
As is ANSI standard, there needs to be a question mark for each parameter. For example, WHERE parentlogid IN (?, ?, ?)", Parameter01, Parameter02, Parameter03).
The following SELECT syntax keywords are supported:
-
SELECT
-
DISTINCT
-
TOP (but not TOP PERCENT)
-
AS (providing aliases for column names or virtual tables)
-
FROM (including subqueries)
-
[INNER] JOIN
-
LEFT JOIN
-
ON
-
WHERE
-
EXISTS
-
ORDER BY
-
DESC / ASC
-
GROUP BY
-
HAVING
-
UNION ALL
The following operators are supported for expressions:
-
NOT AND
-
OR
-
=, >, >=, <, <=, <>, !=
-
IS
-
IN
-
LIKE (however only prefix searches are allowed, for example, "MySearch%")
-
BETWEEN
-
+, -, *, /
The following SQL aggregate functions are supported:
-
SUM
-
COUNT
-
MIN
-
MAX
-
AVG
The following SQL scalar functions are supported:
-
CONCAT
-
CONCAT_WS
-
REPLACE
-
COALESCE
-
ABS
-
EXP
-
IIF
-
LEN
-
LOWER
-
LTRIM
-
RTRIM
-
SUBSTRING
-
ISNULL
-
POWER
-
ROUND
-
CEILING
-
FLOOR
-
SQRT
-
TRIM
-
UPPER
In addition, the following custom j5 scalar functions can be used to convert between SQL Date/Time values and Spreadsheet Date/Time serial numbers:
-
N
-
D