1.1

SELECT (AVG(SUBTOTAL/QTY)) FROM TRADE.PORTFOLIO F WHERE F.SID
= ? AND F.TID =? AND F.QTY <> 0
HAVING Clause
Restrict the results of a GROUP BY in a SelectExpression.
Syntax
HAVING searchCondition
Description
A HAVING clause restricts the results of a GROUP BY in a SelectExpression . The HAVING clause is applied
to each group of the grouped table, much as a WHERE clause is applied to a select list. If there is no GROUP
BY clause, the HAVING clause is applied to the entire result as a single group. The SELECT clause cannot refer
directly to any column that does not have a GROUP BY clause. It can, however, refer to constants, aggregates,
and special registers.
The searchCondition, which is a specialized booleanExpression, can contain only grouping columns (see GROUP
BY Clause), columns that are part of aggregate expressions, and columns that are part of a subquery. For example,
the following query is illegal, because the column AVAILQTY is not a grouping column, it does not appear
within an aggregate, and it is not within a subquery.
Aggregates in the HAVING clause do not need to appear in the SELECT list. If the HAVING clause contains
a subquery, the subquery can refer to the outer query block if and only if it refers to a grouping column.
Example
SELECT AVG (QTY), CID FROM TRADE.PORTFOLIO GROUP BY CID HAVING
AVAILQTY > 150
-- Find the average QTY held by a customer grouped on Customer
ID,
-- only when the customer holds atleast two different stocks
SELECT AVG (QTY), CID FROM TRADE.PORTFOLIO GROUP BY CID HAVING
COUNT(*) > 2
OFFSET and FETCH FIRST Clauses
Limit the number of rows returned in a result set.
Syntax
OFFSET integer-literal { ROW | ROWS }
FETCH { FIRST | NEXT } [integer-literal] { ROW | ROWS } ONLY
Description
Note: You cannot use the OFFSET or FETCH FIRST clause in a query used to create a view. See CREATE
VIEW on page 495.
The OFFSET clause provides a way to skip a number of rows in a result set before returning any rows.
The FETCH FIRST clause, which can be combined with the OFFSET clause, limits the number of rows that are
returned in the result set. You can use the FETCH FIRST clause to retrieve only a few rows from a large result
set. FETCH FIRST is frequently used with an ORDER BY clause.
vFabric SQLFire User's Guide520
vFabric SQLFire Reference