Field use

 

When the grouping function is used, all fields in the record format for the open query file (FORMAT parameter) and all key fields (KEYFLD parameter) must be either grouping fields (specified on the GRPFLD parameter) or mapped fields (specified on the MAPFLD parameter).

Mapped fields are defined using only grouping fields, constants, and aggregate functions. The aggregate functions are: %AVG, %COUNT, %MAX (using only one operand), %MIN (using only one operand), %STDDEV, %SUM, and %VAR. Group processing is required in the following cases:

• When you specify grouping field names on the GRPFLD parameter

• When you specify group selection values on the GRPSLT parameter

• When a mapped field that you specified on the MAPFLD parameter uses an aggregate function in its definition

Fields that have any of the large object data types: BLOB, CLOB, or DBCLOB, can only be read using the Copy From Query File (CPYFRMQRYF) command or Structured Query Language (SQL). Large object field data cannot be directly accessed from an open query file. The CPYFRMQRYF command must be used to access large object fields from an open query file. A field with a large object data type (BLOB, CLOB or DBCLOB) cannot be specified on these OPNQRYF parameters: KEYFLD, UNIQUEKEY, JFLD, and GRPFLD.

Fields of type DATALINK might not appear in selection, grouping, ordering, or joins. If a DATALINK field appears in that format, it will be returned in its unprocessed form, as it exists in the data space.

Fields contained in a record format, identified on the FILE parameter, and defined (in the DDS used to create the file) with a usage value of N (neither input nor output) cannot be specified on any parameter of the OPNQRYF command. Only fields defined as either I (input-only) or B (both input and output) usage can be specified. Any fields with usage defined as N in the record format identified on the FORMAT parameter are ignored by the OPNQRYF command.

Fields in the open query file records normally have the same usage attribute (input-only or both input and output) as the fields in the record format identified on the FORMAT parameter, with the exceptions noted later in this topic. If the file is opened for any option (OPTION parameter) that includes output or update and any usage, and if any B (both input and output) field in the record format identified on the FORMAT parameter is changed to I (input only) in the open query file record format, then an information message is sent by the OPNQRYF command. If you request join processing or group processing, or if you specify UNIQUEKEY processing, all fields in the query records are given input-only use. Any mapping from an input-only field from the file being processed (identified on the FILE parameter) is given input-only use in the open query file record format. Fields defined using the MAPFLD parameter are normally given input-only use in the open query file. A field defined on the MAPFLD parameter is given a value that matches the use of its constituent field if all of the following conditions are true:

• Input-only is not required because of any of the conditions previously described in this topic.

• The field-definition expression specified on the MAPFLD parameter is a field name (no operators or built-in functions).

• The field used in the field-definition expression exists in one of the file, member, or record formats specified on the FILE parameter (not in another field defined using the MAPFLD parameter).

• The base field and the mapped field are compatible field types (the mapping does not mix numeric and character field types, unless the mapping is between zoned and character fields of the same length).

• If the base field is binary with nonzero decimal precision, the mapped field must also be binary and have the same precision.

 

Parent topic:

Using Open Query File (OPNQRYF) command