HAVING clause

The syntax of the HAVING clause is as follows:

HAVING <BooleanExpression>

You can use the HAVING clause with any Boolean expression, such as:

• Numeric and string value comparison: {= , <>, <, <=, >, >=}
• Null value evaluation: <attribute> IS {NULL, NOT NULL, EMPTY, NOT EMPTY}
• Set operations: such as SUBSET and IS_MEMBER_OF
• Grouping keys of the source statement: <attribute-list> IN <source-statement>

In the following example, the results include only sales representatives who generated at least $10,000:

RETURN Reps AS
SELECT SUM(Amount) AS SalesTotal
FROM SaleState
GROUP BY SalesRep
HAVING SalesTotal > 10000

Note that HAVING clauses may refer only to attributes defined in the same statement (such as aliased attributes defined by a SELECT clause). For example, this is an invalid statement:

// Invalid because "Price" is not defined in the statement (i.e., Price is a collection attribute).
Return results AS
SELECT SUM(Price) AS TotalPrices
FROM SaleState
GROUP BY WineType
HAVING Price > 100

The invalid statement example would return this error message:

In statement "results": In HAVING clause: Local statement attribute "Price" is not in scope 

To correct the error, replace the local statement attribute (Price) with an attribute defined in the statement (TotalPrices):

// Valid because "TotalPrices" is defined in the statement.
Return results AS
SELECT SUM(Price) AS TotalPrices
FROM SaleState
GROUP BY WineType
HAVING TotalPrices > 100