HISTOGRAM
|
[MULTI-FETCH-clause]
[multi-fetch-factor] [IN ] [FILE ]
view-name |
|||||
[PASSWORD= operand2]
|
||||||
[IN ]
|
[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 explanations 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
The HISTOGRAM
statement is used to read the values of a
database field which is defined as a descriptor, subdescriptor or
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.
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.
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
For this statement, the specified limit has priority over
a limit set with a If a smaller limit is set with the
Note: |
MULTI-FETCH-clause
|
MULTI-FETCH Clause:
See MULTI-FETCH Clause below. |
view-name
|
View Name:
As The view must not contain any other fields apart from the
field used in the 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 In reporting mode, view-name is
the name of a DDM if no |
PASSWORD=operand2
|
PASSWORD Clause:
The |
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.
Examples of |
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 For further information, see Specifying Starting/Ending Values below. |
WHERE
logical-condition |
WHERE Clause:
The The descriptor specified in the The syntax for a
|
END-HISTOGRAM
|
End of HISTOGRAM Statement:
In structured mode, the Natural reserved word
In reporting mode, the Natural statement
|
LOOP |
Note:
This clause can only be used for Adabas or
DB2 databases.
MULTI-FETCH
|
|
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.
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 ]
|
|
[VALUES ]
operand5
|
THRU
|
operand6 | |||||||||
ENDING AT
|
Syntax Option 2:
[STARTING ]
|
|
[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:
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.
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
For SQL databases, see *NUMBER for SQL Databases in the System Variables documentation. |
*ISN |
The system variable
|
*COUNTER |
The system variable
*COUNTER
contains a count of the total number of values which have been read (after
evaluation of the WHERE clause).
|
** 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
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 '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
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 '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
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
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