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