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)
    • 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
    • NOTE: construct DISTINCT col1, col2, col3 is NOT yet suppored
  • FROM
    • Strictly one service name, no alias
  • 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.
  • ORDER BY
    • You can order on any column in the result. (to be fixed later to allow you to also sort on non-selected columns in the service)

...

The following things are next on the agenda:

  • DISTINCT values
  • Caching of services data in memory with validity time-out
  • Caching of queries in memory with validity time-out
  • prepared statements
  • writing to a service transformation (INSERT INTO)