# Supported SQL clauses

The Pentaho Data Service supports the following clauses.

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/advanced-topics-pentaho-data-integration-overview/pentaho-data-services/test-a-pentaho-data-service/examine-test-results/pentaho-data-service-sql-support-reference-and-other-development-considerations/supported-sql-clauses.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.