Hitachi Vantara Pentaho Community Wiki

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • SELECT:
    • * is expanded to include all rows
    • 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
    • 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.
  • FROM
    • 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.
  • WHERE
    • 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
  • HAVING
    • 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.

QlikView

http://tiqview.tumblr.com/post/29820190073/stream-data-from-pentaho-kettle-into-qlikview-via-jdbc

Future

The following things are next on the agenda:

...