HISTOGRAM
|
|
ALL
|
|
[MULTI-FETCH-clause
] [multi-fetch-factor] [IN] [FILE]
view-name
|
||
| (operand1) | ||||||
[PASSWORD=operand2]
|
||||||
|
|
[IN]
|
|
ASCENDING
|
|
[SEQUENCE]
|
|
DESCENDING |
||||||
VARIABLE
operand3 |
||||||
DYNAMIC operand3 |
||||||
[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 HISTOGRAM Statement (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:
| operand1 / ALL |
Processing Loop Limit:
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 | See MULTI-FETCH Clause below. | |
| view-name |
As view-name, you specify the name of a
view, which is defined either within a 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 WHERE clause may be used to specify an additional selection
criterion (logical-condition) which is evaluated
after a value has been read and before any processing is
performed on the value (including the The descriptor specified in the The syntax for a logical-condition is described in the section Logical Condition Criteria (in the Programming Guide). System Variables
The Natural system variables
|
|
| *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).
|
|
| END-HISTOGRAM | The Natural reserved word
END-HISTOGRAM must be used to end the HISTOGRAM
statement.
|
|
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 Managment 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:
1
|
|
[STARTING]
|
|
WITH
|
|
[VALUES]
operand5
|
|
|
|
THRU
|
|
operand6 |
|
|
FROM
|
ENDING AT
|
Syntax Option 2:
2
|
[STARTING]
|
|
WITH FROM
|
|
[VALUES]
operand5
|
TO
|
operand6 |
Syntax Option 3:
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:
** 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