IBM SC41-5210-04 manual Los Angeles Arcadia

Summary-only output of a Query for iSeries report to a database file

The data sent to a database file, when summary-only output is selected, is one of the following:

vIf no summary functions or report breaks have been selected, the output is a single record containing a count of the records selected by your query.

vIf summary functions but no report breaks have been selected, the output is a single record containing summary function values.

vIf report breaks but no summary functions have been selected, the output is a record containing report break values for each report break.

vIf both summary functions and report breaks have been selected, the output is a record containing report break values and summary function values for every report break and for the final totals.

For a better understanding of summary-only output to a database file, compare the summary database file output with a printed report from the same query. The data produced is the same (if no summaries have been suppressed), but the layout of the data is different. If you print the query definition for a query with output sent to a database file, the record format layout is included.

The information in each summary-only record that is output to a database file has the following format:

vThe first position of each record contains a break level number.

vThe second position of each record contains an overflow indicator.

vThe third position of each record begins the actual data.

The break level is a number from 0 through 6, which identifies the different report break levels output to a record. A 0 identifies a final total (summary) record. The numbers 1 through 6 identify a report break and correspond to the level number associated with the report break.

The overflow indicator indicates when data has overflowed in 1 or more fields in this record. The indicator position is blank if overflow has not occurred. An asterisk (*) appears if any calculated field overflows. The field that overflowed is filled with the maximum value for a field of that type and size (a string of asterisks

***** appears for the value in a printed or displayed report).

The output data is grouped together by field with the report break values followed by any summary function values for the field. The data for break fields with priority lower than the current break level is considered null. If the corresponding field in the output file is not null capable, Query for iSeries uses the null-value defaults (like blanks for character data). Summary data is displayed or printed in the following order: total, average, minimum, maximum, and count. If a field is totaled, Query adds 3 digits to the field length to hold the total (up to a maximum of 31 digits). Count summary values are 7 characters long and are zoned decimal. Break and summary values for packed and binary data are converted to zoned decimal format when saved in a database file. Date, time, and timestamp data is saved in internal format. DBCS-graphic data is saved without shift-out and shift-in characters.

As an example, assume you have an input file containing personnel information about all state employees. You set up a query that requests total salary, average salary, and the number of employees in each county and city. The query sorts on two fields: CITY within COUNTY. Report breaks are defined for these fields and the Salary field for each county is totaled, averaged, and counted.

If you ran this query and selected summary-only output, the following seven records would be sent to a database file:

2	Los Angeles	Arcadia	00007000000	03500000	0000002
2	Los Angeles	Glendale	00009000000	04500000	0000002
1	Los Angeles	.........	00016000000	04000000	0000004
2	Orange	Costa Mesa	00006000000	03000000	0000002
2	Orange	Irvine	00008000000	04000000	0000002
1	Orange	.........	00014000000	03500000	0000004
0	.........	.........	00030000000	03750000	0000008

160Query for iSeries Use V5R2