HISTOGRAM

You can limit the number of descriptor values to be processed with the HISTOGRAM statement by specifying operand1 - either as a numeric constant (0 - 4294967295) or as a user-defined variable (containing an integer value).

ALL may optionally be specified to emphasize that all descriptor values are to be processed.

For this statement, the specified limit has priority over a limit set with a LIMIT statement.

If a smaller limit is set with the LT parameter (Limit for Processing Loops), the LT limit applies.

See MULTI-FETCH Clause below.

As view-name, you specify the name of a view, which is defined either within a DEFINE DATA statement or in a separate global or local data area.

The view must not contain any other fields apart from the field used in the HISTOGRAM statement (operand4).

If the field in the view is a periodic-group field or multiple-value field that is defined with an index range, only the first occurrence of that range is filled by the HISTOGRAM statement; all other occurrences are not affected by the execution of the HISTOGRAM statement.

In reporting mode, view-name is the name of a DDM if no DEFINE DATA LOCAL statement is used.

The PASSWORD clause is used to provide a password (operand2) when retrieving data from an Adabas file which is password-protected. See the statements FIND and PASSW for further information.

This clause can only be used for Adabas and SQL databases.

With this clause, you can determine whether the records are to be read in ascending sequence or in descending sequence.

• The default sequence is ascending (which may, but need not, be explicitly specified by using the keyword ASCENDING).

• If the records are to be read in descending sequence, you specify the keyword DESCENDING.

• If, instead of determining it in advance, you want to have the option of determining at runtime whether the records are to be read in ascending or descending sequence, you either specify the keyword VARIABLE or DYNAMIC, followed by a variable (operand3). operand3 has to be of format/length A1 and can contain the value A (for "ascending") or D (for "descending").

  • If keyword VARIABLE is used, the reading direction (value of operand3) is evaluated at start of the HISTOGRAM processing loop and remains same until the loop is terminated, regardless if the operand3 field is altered in the HISTOGRAM loop or not.

  • If keyword DYNAMIC is used, the reading direction (value of operand3) is evaluated before every record fetch in the HISTOGRAM processing loop and may be changed from record to record. This allows to change the scroll sequence from ascending to descending (and vice versa) at any place in the HISTOGRAM loop.

Examples of SEQUENCE clause:

• Example 2 - HISTOGRAM Statement with Records Read in Descending Sequence

• Example 3 - HISTOGRAM Statement Using Variable Sequence

As operand4, a descriptor, subdescriptor, superdescriptor or hyperdescriptor may be specified.

A descriptor contained within a periodic group may be specified with or without an index. If no index is specified, the descriptor will be selected if the value specified is located in any occurrence. If an index is specified, the descriptor will be selected only if the value is located in the occurrence specified by the index. The index specified must be a constant. An index range must not be used.

For a descriptor which is a multiple-value field an index must not be specified; the descriptor will be selected if the value is located in the record regardless of the position of the value.

Starting and ending values may be specified using the keywords STARTING and ENDING (or THRU) followed by a constant or a user-defined variable representing the value with which processing is to begin/end.

For further information, see Specifying Starting/Ending Values below.

The WHERE clause may be used to specify an additional selection criteria (logical-condition) which is evaluated after a value has been read and before any processing is performed on the value (including the AT BREAK evaluation).

The descriptor specified in the WHERE clause must be the same descriptor referenced in the HISTOGRAM statement. No other fields from the selected file are available for processing with a HISTOGRAM statement.

The syntax for a logical-condition is described in the section Logical Condition Criteria (in the Programming Guide).

In structured mode, the Natural reserved word END-HISTOGRAM must be used to end the HISTOGRAM statement.

In reporting mode, the Natural statement LOOP must be used to end the HISTOGRAM statement.

The STARTING FROM and ENDING AT clauses are used to limit reading to a user-specified range of values.

The STARTING FROM clause (= or EQ or EQUAL TO or [STARTING] FROM) determines the starting value for the read operation. If a starting value is specified, reading will begin with the value specified. If the starting value does not exist, the next higher (or lower for a DESCENDING read) value will be returned. If no higher (or lower for DESCENDING) value exists, the HISTOGRAM loop will not be entered.

In order to limit the values to an end-value, you may specify an ENDING AT clause with the terms THRU, ENDING AT or TO, that imply an inclusive range. Whenever the descriptor field exceeds the end-value specified, an automatic loop termination is performed. Although the basic functionality of the TO, THRU and ENDING AT keywords looks quite similar, internally they differ in how they work.

If THRU or ENDING AT is used, only the start-value is supplied to the database, but the end-value check is performed by the Natural runtime system, after the value is returned by the database.

The THRU and ENDING AT options can be used for all databases which support the HISTOGRAM statements.

If the keyword TO is used, both the start-value and the end-value are sent to the database and Natural does not perform checks for value ranges. If the end-value is exceeded, the database reacts in the same way as when "end-of-file" is reached and the database loop is exited. Since the complete range checking is done by the database, the lower-value (of the range) is always supplied in the start-value and the higher-value filled into the end-value, regardless whether you are browsing in ASCENDING or in DESCENDING order.

The system variable *NUMBER contains the number of database records that contain the last value read.

For SQL databases, see *NUMBER for SQL Databases in the System Variables documentation.

The system variable *ISN contains the number of the occurrence in which the descriptor value last read is contained. *ISN will contain 0 if the descriptor is not contained within a periodic group.

*ISN is not available for SQL databases.