Enform Plus Reference Manual

Statements
Enform Plus Reference Manual—422684-001
4-29
LINK OPTIONAL Statement Considerations
record descriptions referenced in the WHERE clause contribute to the logical record,
Enform Plus uses all the terms in the WHERE clause.
•
For a given logical record, Enform Plus supplies null values for any fields that
correspond to a “noncontributing” record description. If your query specifications
include expressions or aggregates that reference these null field values, Enform Plus
computes the expression or aggregate value by using blank values. In some cases,
this use of blanks might cause undesirable results. For example:
°
If you specify that the aggregates COUNT, MAX, or MIN (or a similar user
aggregate) are to be performed for a field containing null values, Enform Plus
will count or compare the null values just like any other value.
°
If you specify that the aggregates SUM or AVG (or a similar user aggregate) are
to be performed for a binary numeric field containing null values, Enform Plus
will include the null values as ASCII blanks in the resulting computations.
°
If you include the UNIQUE specification with an aggregate, the null value will
be considered a UNIQUE value. (This will occur if null values are not stored in
the field for which the aggregate is being computed.)
°
If a binary numeric field that contains null values is included in an arithmetic
expression, Enform Plus will include the null values as ASCII blanks in the
specified computations. Because null values correspond to a very large binary
number, this could result in a very large, and probably, meaningless, result.
If your query involves aggregates, you can eliminate these invalid results by including
the WHERE option with the aggregate. For example, suppose that you want to use the
aggregate COUNT to count the occurrences of no-num, a two-character field that is
declared alphanumeric (PIC XX or TYPE CHARACTER 2) in the record description.
If you specify the aggregate as follows:
COUNT (no-num WHERE no-num <> BLANKS)
Enform Plus does not count any occurrences of no-num that contain null values
because Enform Plus treats null values as blanks in alphanumeric fields.
If no-num was declared numeric (PIC 99) in the record description, you could specify
the aggregate as follows:
COUNT (no-num WHERE no-num <> 0)
Enform Plus does not count any occurrences of no-num that contain null values
because Enform Plus treats null values in numeric fields as zeros.
If no-num was declared to be a two-byte binary numeric field (PIC 99 USAGE IS
COMP or TYPE BINARY 16) in the record description, you could eliminate an invalid
result by specifying:
COUNT (no-num WHERE no-num <> 8224)
Enform Plus does not count any occurrences of no-num that contain null values
because Enform Plus treats null values in two-character binary fields as decimal 8224.