- * is expanded to include all rows
- 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
- Aggregates: SUM, AVG, MIN, MAX
- Alias both with the "AS" keyword and with one or more spaces seperated, for example SUM(sales) AS "Total Sales" or SUM(sales) TotalSales
- Constant expressions are possible, see below in the literals section.
- Calculations on the other hand are not possible yet, perform them in the service transformation for now.
- Strictly one service name, aliasing is possible
- 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.
- You can specify a schema (default is Kettle) but it is currently ignored. It will be translated to a namespace in the near future.
- nested brackets
- AND, OR, NOT if preceded by brackets, for example: NOT ( A = 5 OR C = 3 )
- precedence taken into account
- Literals (String, Integer)
- PARAMETER('parameter-name')='value' (always evaluates to TRUE in the condition)
- <=, =<
- >=, =>
- LIKE (standard % and ? wildcards are converted to .* and . regular expressions)
- REGEX (matches regular expression)
- IS NULL
- IS NOT NULL
- IN ( value, value, value, ... )
- You can put a condition on the IIF expression or it's alias if one is used. (please use identical string literals for expressions)
- GROUP BY
- Group on fields, not IIF() function
- Conditions should be placed on the aggregate construct, not the alias
- Please use identical strings for the expressions, the algorithm is not yet that smart. In other words, if you use "COUNT( * )" in the SELECT clause you should use the same "COUNT( * ) " expression in the HAVING clause, not "COUNT(*)" or any variant of it.
- You can place having conditions on aggregations that do not appear in the SELECT clause.
- ORDER BY
- You can order on any column in the result or not in the result
- You can order on IFF or CASE-WHEN expressions.
Partial success as I'm getting some XML parsing errors. However, adding the aforementioned jar files at least allow you to get back query fields:
To be investigated.
The following things are next on the agenda: