1.1

The WHERE clause causes rows to be filtered from the result based on a boolean expression. Only rows for
which the expression evaluates to TRUE are returned in the result.
The GROUP BY clause groups rows in the result into subsets that have matching values for one or more columns.
GROUP BY clauses are typically used with aggregates.
If there is a GROUP BY clause, the SELECT clause must contain only aggregates or grouping columns. If you
want to include a non-grouped column in the SELECT clause, include the column in an aggregate expression.
For example:
-- List number of customers holding a security
–- average quantity of security ,
-- the security ID,
-- for all securities in the PORTFOLIO table.
-- Arrange the result table in ascending order by average quantity.
SELECT COUNT(*),SID,AVG(QTY)
FROM TRADE.PORTFOLIO
GROUP BY SID
ORDER BY 3
If there is no GROUP BY clause, but a SelectItem contains an aggregate not in a subquery, the query is implicitly
grouped. The entire table is the single group.
The HAVING clause restricts a grouped table, specifying a search condition (much like a WHERE clause) that
can refer only to grouping columns or aggregates from the current scope. The HAVING clause is applied to each
group of the grouped table. If the HAVING clause evaluates to TRUE, the row is retained for further processing.
If the HAVING clause evaluates to FALSE or NULL, the row is discarded. If there is a HAVING clause but no
GROUP BY, the table is implicitly grouped into one group for the entire table.
SQLFire processes a SelectExpression in the following order:
• FROM clause
• WHERE clause
• GROUP BY (or implicit GROUP BY)
• HAVING clause
• SELECT clause
The result of a SelectExpression is always a table.
When a query does not have a FROM clause (when you are constructing a value, not getting data out of a table),
you use a VALUES expression, not a SelectExpression. For example:
VALUES CURRENT_TIMESTAMP
The * wildcard
* expands to all columns in the tables in the associated FROM clause.
table-Name .* and correlation-Name.* expand to all columns in the identified table. That table must be listed in
the associated FROM clause.
Naming columns
You can name a SelectItem column using the AS clause. If a column of a SelectItem is not a simple
ColumnReference expression or named with an AS clause, it is given a generated unique name.
These column names are useful in several cases:
• They are made available on the JDBC ResultSetMetaData.
• They are used as the names of the columns in the resulting table when the SelectExpression is used as a table
subquery in a FROM CLAUSE.
527
SQL Language Reference