HISTOGRAM

#### Search Results

Search

# HISTOGRAM

`HISTOGRAM` |  | `ALL` (operand1) |  | [MULTI-FETCH-clause] [multi-fetch-factor] [`IN`] [`FILE`] view-name
[`PASSWORD=`operand2]
| [`IN`] |  | `ASCENDING` `DESCENDING` `VARIABLE` operand3 `DYNAMIC` operand3 |  | [`SEQUENCE`] |
[`VALUE`] [`FOR`] [`FIELD`] operand4
[STARTING/ENDING-clause]
[`WHERE` logical-condition]
statement
`END-HISTOGRAM` | (structured mode only)
`LOOP` | (reporting mode only)

This document covers the following topics:

- Function

- Restrictions

- Syntax Description

- System Variables Available with HISTOGRAM

- Examples

For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.

Related Statements: `ACCEPT/REJECT` | `AT BREAK` | `AT START OF DATA` | `AT END OF DATA` | `BACKOUT TRANSACTION` | `BEFORE BREAK PROCESSING` | `DELETE` | `END TRANSACTION` | `FIND` | `GET` | `GET SAME` | `GET TRANSACTION DATA` | `LIMIT` | `PASSW` | `PERFORM BREAK PROCESSING` | `READ` | `RETRY` | `STORE` | `UPDATE`

Belongs to Function Group: Database Access and Update

## Function

The `HISTOGRAM` statement is used to read the values of a database field which is defined as a descriptor, subdescriptor, or a superdescriptor. The values are read directly from the Adabas inverted lists or VSAM index. The `HISTOGRAM` statement causes a processing loop to be initiated but does not provide access to any database fields other than the field specified in the `HISTOGRAM` statement.

See also the following sections in the Programming Guide:

- HISTOGRAM Statement

- Loop Processing

- Referencing of Database Fields Using (r) Notation

Note
For SQL databases: `HISTOGRAM` returns the number of rows which have the same value in a specific column.

## Restrictions

- This statement cannot be used with Entire System Server.

  When applied to a VSAM database, the `HISTOGRAM` statement is only valid for KSDS and ESDS.

## Syntax Description

Operand Definition Table:

Operand | Possible Structure | Possible Formats | Referencing Permitted | Dynamic Definition
--- | --- | --- | --- | ---
`operand1` | C | S |  |  |  |  |  | N | P | I |  | B * |  |  |  |  |  |  | yes | no
`operand2` | C | S |  |  |  | A |  |  |  |  |  |  |  |  |  |  |  |  | yes | no
`operand3` |  | S |  |  |  | A |  |  |  |  |  |  |  |  |  |  |  |  | yes | no
`operand4` |  | S |  |  |  | A |  | N | P | I | F | B | D | T | L |  |  |  | no | no

* Format B of `operand1` may be used only with a length of less than or equal to 4.

Syntax Element Description:

Syntax Element | Description
--- | ---
`operand1 / ALL` | Number of Descriptor Values: 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. Note If you wish to process a 4-digit number of descriptor values, specify it with a leading zero `(0nnnn)`; because Natural interprets every 4-digit number enclosed in parentheses as a line-number reference to a statement. `operand1` is evaluated when the `HISTOGRAM` loop is entered. If the value of `operand1` is modified within the `HISTOGRAM` loop, this does not affect the number of values read.
`MULTI-FETCH-clause` | MULTI-FETCH Clause: See MULTI-FETCH Clause below.
`view-name` | View Name: 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.
`PASSWORD=operand2` | PASSWORD Clause: 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.
`SEQUENCE` | SEQUENCE Clause: This clause can only be used for Adabas, VSAM 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
`operand4` | Descriptor: 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-ENDING-clause` | STARTING/ENDING Clause: 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.
`WHERE logical-condition` | WHERE Clause: 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).
`END-HISTOGRAM` | End of HISTOGRAM Statement: 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.
`LOOP`

#### MULTI-FETCH Clause

Note
This clause can only be used for Adabas or Db2 databases.

`MULTI-FETCH` |  | `ON` `OFF` [`OF`] multi-fetch-factor |

For more information, see the section MULTI-FETCH Clause (Adabas) in the Programming Guide or Multiple Row Processing (SQL) in the Natural for Db2 part in the Database Management System Interfaces documentation.

### Specifying Starting/Ending Values

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.

If a starting value is specified and the value is not present, the next higher value is used as the starting value. If no higher value is present, the `HISTOGRAM` loop will not be entered.

If an ending value is specified, values will be read up to and including the ending value.

Hexadecimal constants may be specified as a starting or ending value for descriptors of format A or B.

Syntax Option 1:

| [`STARTING`] |  | `WITH` `FROM` |  | [`VALUES`] operand5 |  |  |  |  | `THRU` |  | operand6 |
`ENDING AT`

Syntax Option 2:

[`STARTING`] |  | `WITH` `FROM` |  | [`VALUES`] operand5 | `TO` | operand6

Syntax Option 3:

| < |  | operand5
`LT`
`LESS THAN`
>
`GT`
`GREATER THAN`
`<=`
`LE`
`LESS EQUAL`
`>=`
`GE`
`GREATER EQUAL`

Note
If the comparators of Diagram 3 are used, the options `ENDING AT`, `THRU` and `TO` may not be used. These comparators are also valid for the `READ` statement.

Operand Definition Table:

Operand | Possible Structure | Possible Formats | Referencing Permitted | Dynamic Definition
--- | --- | --- | --- | ---
`operand5` | C | S |  |  |  | A | U | N | P | I | F | B | D | T | L |  |  |  | yes | no
`operand6` | C | S |  |  |  | A | U | N | P | I | F | B | D | T | L |  |  |  | yes | no

Syntax Element Description:

Syntax Element | Description
--- | ---
`STARTING FROM ... ENDING AT |TO` | STARTING FROM / ENDING AT Clauses: 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.
`THRU | ENDING AT` | THRU / ENDING AT Option: 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.
`TO` | Range: 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.

Note
The result of `READ`/`HISTOGRAM` `THRU`/`ENDING AT` might differ from the result of `READ`/`HISTOGRAM` `TO` if Natural and the accessed database reside on different platforms with different collating sequences.

## System Variables Available with HISTOGRAM

The Natural system variables `*ISN`, `*NUMBER`, and `*COUNTER` are available with the `HISTOGRAM` statement.

`*NUMBER` and `*ISN` are only set after the evaluation of the `WHERE` clause. They must not be used in the logical condition of the `WHERE` clause.

System Variable | Explanation
--- | ---
`*NUMBER` | 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.
`*ISN` | 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 and VSAM databases.
`*COUNTER` | The system variable `*COUNTER` contains a count of the total number of values which have been read (after evaluation of the `WHERE` clause).

## Examples

- Example 1 - HISTOGRAM Statement

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

- Example 3 - HISTOGRAM Statement Using Variable Sequence

### Example 1 - HISTOGRAM Statement

    ** Example 'HSTEX1S': HISTOGRAM (structured mode)
    ************************************************************************
    DEFINE DATA LOCAL
    1 EMPLOY-VIEW VIEW OF EMPLOYEES
      2 CITY
    END-DEFINE
    *
    LIMIT 8
    HISTOGRAM EMPLOY-VIEW CITY STARTING FROM 'M'
      DISPLAY NOTITLE
              CITY 'NUMBER OF/PERSONS' *NUMBER *COUNTER
    END-HISTOGRAM
    *
    END

#### Output of Program HSTEX1S:

            CITY          NUMBER OF     CNT
                           PERSONS
    -------------------- ----------- -----------

    MADISON                        3           1
    MADRID                        41           2
    MAILLY LE CAMP                 1           3
    MAMERS                         1           4
    MANSFIELD                      4           5
    MARSEILLE                      2           6
    MATLOCK                        1           7
    MELBOURNE                      2           8

Equivalent reporting-mode example: `HSTEX1R`.

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

    ** Example 'HSTDSCND': HISTOGRAM (with DESCENDING)
    ***********************************************************************
    DEFINE DATA LOCAL
    1 EMPL VIEW OF EMPLOYEES
      2 NAME
    END-DEFINE
    *
    HISTOGRAM (10) EMPL IN DESCENDING SEQUENCE FOR NAME FROM 'ZZZ'
      DISPLAY NAME *NUMBER
    END-HISTOGRAM
    END

#### Output of Program HSTDSCND:

    Page      1                                                  05-01-13  13:41:03

            NAME            NMBR
    -------------------- -----------

    ZINN                           1
    YOT                            1
    YNCLAN                         1
    YATES                          1
    YALCIN                         1
    YACKX-COLTEAU                  1
    XOLIN                          1
    WYLLIS                         2
    WULFRING                       1
    WRIGHT                         1

### Example 3 - HISTOGRAM Statement Using Variable Sequence

    ** Example 'HSTVSEQ': HISTOGRAM (with VARIABLE SEQUENCE)
    ***********************************************************************
    DEFINE DATA LOCAL
    1 EMPL VIEW OF EMPLOYEES
      2 NAME
    *
    1 #DIR      (A1)
    1 #STARTVAL (A20)
    END-DEFINE
    *
    SET KEY PF3 PF7 PF8
    *
    MOVE 'ADKINSON' TO #STARTVAL
    *
    HISTOGRAM (9) EMPL FOR NAME FROM #STARTVAL
      WRITE NAME *NUMBER
      IF *COUNTER = 5
        MOVE NAME TO #STARTVAL
      END-IF
    END-HISTOGRAM
    *
    #DIR := 'A'
    *
    REPEAT
      HISTOGRAM EMPL IN VARIABLE #DIR SEQUENCE
                FOR NAME FROM #STARTVAL
        MOVE NAME TO #STARTVAL
        INPUT NO ERASE  (IP=OFF AD=O)
              15/01 NAME *NUMBER
              //   'Direction:' #DIR
              //   'Press PF3 to stop'
              /    '      PF7 to go step back'
              /    '      PF8 to go step forward'
              /    '      ENTER to continue in that direction'
        /*
        IF *PF-KEY = 'PF7' AND #DIR = 'A'
          MOVE 'D' TO #DIR
          ESCAPE BOTTOM
        END-IF
        IF *PF-KEY = 'PF8' AND #DIR = 'D'
          MOVE 'A' TO #DIR
          ESCAPE BOTTOM
        END-IF
        IF *PF-KEY = 'PF3'
          STOP
        END-IF
      END-HISTOGRAM
      /*
      IF *COUNTER(0250) = 0
        STOP
      END-IF
    END-REPEAT
    END

#### Output of Program HSTVSEQ:

    Page      1                                                  05-01-13  13:50:31

    ADKINSON                       8
    AECKERLE                       1
    AFANASSIEV                     2
    AHL                            1
    AKROYD                         1
    ALEMAN                         1
    ALESTIA                        1
    ALEXANDER                      5
    ALLEGRE                        1

    MORE

#### After pressing ENTER:

    Page      1                                                  05-01-13  13:50:31

    ADKINSON                       8
    AECKERLE                       1
    AFANASSIEV                     2
    AHL                            1
    AKROYD                         1
    ALEMAN                         1
    ALESTIA                        1
    ALEXANDER                      5
    ALLEGRE                        1

    AKROYD                         1

    Direction: A

    Press PF3 to stop
          PF7 to go step back
          PF8 to go step forward
          ENTER to continue in that direction

### On this page

- Function
- Restrictions
- Syntax Description
- System Variables Available with HISTOGRAM
- Examples