HAVING

This item is optional. It specifies which summary record is transferred.

Pay particular attention to the difference between HAVING and WHERE. WHERE operates on each record within a certain group. HAVING, on the other hand, operates only on summary records (records that contain summary information for each group).

With this item, you can specify one or more conditions that a summary record must satisfy prior to being transferred.

As the conditions, specify the tests that should be applied to the summary records. The specified test conditions are applied to all summary records, only those summary records that pass the tests are transferred. To transfer all summary records, leave the HAVING item blank.

The format of the conditions is as follows:

function (field-name)  test  value

This indicates:

function

This is a function supported for SELECT. See the description of SELECT in this section for details of these functions.

field-name

This is the field defined by the record format specified in FROM. A field name is acceptable even when it has not been specified in SELECT.

Test

This is the comparison type for functions. The types are listed below.

=: Equal to
<> or ><: Not equal
>: Greater than
>=: Greater than or equal to
<: Less than
<=: Less than or equal to

value

This is a function operating on certain fields or a constant. See WHERE for details of constants, expressions, and tests.

Note: A comma is treated as a decimal point. Therefore, do not separate numbers with commas.

Test conditions can be combined by using logical AND or logical OR. When both AND and OR are specified, AND comparison is performed first. Up to 50 tests can be specified. By using parentheses, the operation order can be modified, or a description can be added to an operation. For example, you can specify:

COUNT(*) >=2 AND MAX(PRICE) > 100

In this case, the following conditions are applied concurrently: groups to be transferred must contain more than one record, and the summary records in such groups are transferred only when the maximum price is greater than 100.

If the desired field cannot be found, selecting Browse displays a list of the names of all fields in the record.

The type, length, digit, and number of decimal places of the value returned for each function are:

            Type     Length    Digit       Decimal Places
SUM         Packed     16       31    (Same as tested field)
AVG         Packed     16       31     31 (Total of the digit
                                           and decimal places
                                           of the field)
COUNT       Binary      4       10         0
MAX              (Same as tested field)
MIN              (Same as tested field)