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:

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:

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 DL/I databases or 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:

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:

../graphics/cbo14.gif

<

../graphics/cbc14.gif

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 '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