| SELECT |
- COUNT(field)
- COUNT(*)
- COUNT(DISTINCT field)
- DISTINCT <fields>
- IIF (condition, true-value or field, false-value or
field)
- CASE WHEN condition THEN true-value ELSE false-value END
- SUM
- AVG
- MIN
- MAX
- Aliases with both the AS keyword and with one or more spaces
separated. For example: SUM(sales) AS "Total Sales" or SUM(sales)
TotalSales
- Constant expressions are possible. See the Supported SQL literals
section for more details.
|
| FROM |
- Only one Pentaho service name is permitted.
- You can use aliases for the Pentaho
service name.
- You can omit the service name to query from an empty row or you can query from
dual, for example SELECT 1 or SELECT 1 FROM
dual are the same. (Dual is a special one row, one
column table supported by some database vendors.)
|
| WHERE |
- Nested brackets
- AND, OR, NOT if followed by
brackets. For example: NOT (A=5 OR C=3).
- Precedence is considered.
- Literals (String and Integer)
- PARAMETER('parameter-name')='value'. Note that this always
evaluates to TRUE in the condition.
- =
- <
- >
- <=, =<
- >=, =>
- <>
- LIKE is supported. The standard % and
? wildcards are converted to .* and
. regular expressions.
- REGEX matches the regular expression.
- IS NULL
- IS NOT NULL
- IN The syntax for multiple values is: value, value,
value, ...
- You can put a condition on the IIF expression or its alias if
one is used. Use identical string literals for expressions.
- DATE_TO_STR (date-field, <mask>). Masks are strings. For
example: 'yyyy' and 'yyyy-MM-dd'. Note that
the character '(apostrophe) should be escaped in masks
by using two apostrophes instead of one.
|
| GROUP BY |
- Group on fields are supported, not the IIF() function.
|
| LIMIT |
- You can retrieve a specific amount of rows from a result set using the
LIMIT keyword. For example, this query returns the first 10
rows (#1-10) from the result set: SELECT * FROM data_service LIMIT
10;
- You can also specify an offset for returning as specific amount of rows using
the LIMIT keyword or LIMIT/OFFSET keywords.
This queries returns 10 rows from the result set, but the results are offset by
five rows so you see results for rows #6 - 15: SELECT * FROM
data_service LIMIT 5, 10;
- You can also use the OFFSET keyword to retrieve the same
results: SELECT * FROM data_service LIMIT 10 OFFSET 5;
|
| HAVING |
- Conditions should be placed on the aggregate construct, not the alias.
- Please use identical strings for the expressions including spaces. For
example, if you use put spaces before and after the asterisk for COUNT(
* ) in the SELECT clause, do the same for the
HAVING clause.
- Put HAVING conditions on aggregations that do not appear in
the SELECT clause.
|
| ORDER BY |
- You can order on any column even if it is not in the result.
- You can order on IIF or CASE-WHEN
expressions.
|