The Open Query File (OPNQRYF) command allows you to specify a record format for the query records.
You can also define fields that are mapped from existing fields through numeric and character operations.
Defining fields mapped from existing fields
The Open Query File (OPNQRYF) command supports mapped field definitions. That is, you can define fields that are mapped or derived from other fields.
Mapped field definitions:
• Allow you to create internal fields that specify selection values, as shown in “Example 7: Dynamically selecting records” on page 127.
• Allow you to avoid confusion when the same field name occurs in multiple files, as shown in “Example 1: Dynamically joining database files” on page 144.
• Allow you to create fields that exist only in the format to be processed, but not in the database itself.
This allows you to perform translate, substring, concatenation, and complex mathematical operations.
The following examples describe this function.
Example 1: Defining fields mapped from existing fields This example shows the use of mapped fields.
Assume that you have the Price and Qty fields in the record format. You can multiply one field by the other by using the Open Query File (OPNQRYF) command to create the mapped Exten field. You want FILEA to be processed, and you have already created FILEAA. The record formats for the files contain the following fields.
FILEA FILEAA
Order Order
FILEA FILEAA
Item Item
Qty Exten
Price Brfdsc
Descrp
The Exten field is a mapped field. Its value is determined by multiplying Qty times Price. It is not
necessary to have either the Qty or Price field in the new format, but they can exist in that format, too, if you want. The Brfdsc field is a brief description of the Descrp field (it uses the first 10 characters).
Assume that you have specified PGMF to process the new format. To create this program, use FILEAA as the file to read. You can specify:
OVRDBF FILE(FILEAA) TOFILE(FILEA) SHARE(*YES) OPNQRYF FILE(FILEA) FORMAT(FILEAA) +
MAPFLD((EXTEN 'PRICE * QTY') + (BRFDSC 'DESCRP'))
CALL PGM(PGMF) /* Created using file FILEAA as input */
CLOF OPNID(FILEA) DLTOVR FILE(FILEAA)
Notice that the attributes of the Exten field are those defined in the record format for FILEAA. If the value calculated for the field is too large, an exception is sent to the program.
It is not necessary to use the substring function to map to the Brfdsc field if you only want the characters from the beginning of the field. The length of the Brfdsc field is defined in the FILEAA record format.
All fields in the format specified on the FORMAT parameter must be described on the OPNQRYF
command. That is, all fields in the output format must either exist in one of the record formats for the files specified on the FILE parameter or be defined on the MAPFLD parameter. If you have fields in the format on the FORMAT parameter that your program does not use, you can use the MAPFLD parameter to place zeros or blanks in the fields. Assume the Fldc field is a character field and the Fldn field is a numeric field in the output format, and you are using neither value in your program. You can avoid an error on the OPNQRYF command by specifying:
MAPFLD((FLDC ' " " ')(FLDN 0))
Notice quotation marks enclose a blank value. By using a constant for the definition of an unused field, you avoid having to create a unique format for each use of the OPNQRYF command.
Example 2: Defining fields mapped from existing fields This example shows the use of built-in functions.
Assume that you want to calculate a mathematical function that is the sine of the Fldm field in FILEA.
First, you can create a file (assume that it is called FILEAA) with a record format that contains the following fields.
FILEA FILEAA
Code Code
Fldm Fldm
Sinm
You can then create a program (assume PGMF) using FILEAA as input and specify:
OVRDBF FILE(FILEAA) TOFILE(FILEA) SHARE(*YES) OPNQRYF FILE(FILEA) FORMAT(FILEAA) +
MAPFLD((SINM '%SIN(FLDM)'))
CALL PGM(PGMF) /* Created using file FILEAA as input */
CLOF OPNID(FILEA)
The built-in function %SIN calculates the sine of the field specified as its argument. Because the Sinm field is defined in the format specified on the FORMAT parameter, the Open Query File (OPNQRYF) command converts its internal definition of the sine value (in binary floating point) to the definition of the Sinm field. This technique can be used to avoid certain high-level language restrictions on the use of binary floating-point fields. For example, if you defined the Sinm field as a packed decimal field, PGMF can be written in any high-level language, even though the value was built using a binary floating-point field.
There are many other functions besides sine that can be used.
Related reference Built-in functions
These built-in functions are supported for an expression that is used to define a derived field on the MAPFLD parameter or for a complex selection operand specified on the QRYSLT or GRPSLT parameter.
Restricted built-in functions
Some built-in functions are restricted in the way certain relational operators are specified on the QRYSLT and GRPSLT parameters.
Example 3: Defining fields mapped from existing fields
This example shows the use of mapped fields and built-in functions.
Assume, in “Example 2: Defining fields mapped from existing fields” on page 137, that a field called Fldx also exists in FILEA and the Fldx field has appropriate attributes used to hold the sine of the Fldm field.
Also, assume that you are not using the content of the Fldx field. You can use the MAPFLD parameter to change the content of a field before passing it to your high-level language program. For example, you can specify:
OVRDBF FILE(FILEA) SHARE(*YES)
OPNQRYF FILE(FILEA) MAPFLD((FLDX '%SIN(FLDM)'))
CALL PGM(PGMF) /* Created using file FILEA as input */
CLOF OPNID(FILEA) DLTOVR FILE(FILEA)
In this case, you do not need to specify a different record format on the FORMAT parameter. (The default uses the format of the first file on the FILE parameter.) Therefore, the program is created by using FILEA.
When using this technique, you must ensure that the field you redefine has attributes that allow the calculated value to process correctly. The least complicated approach is to create a separate file with the specific fields you want to process for each query.
You can also use this technique with a mapped field definition and the %XLATE function to translate a field so that it appears to the program in a different manner than what exists in the database. For example, you can translate a lowercase field so the program only sees uppercase.
The sort sequence and language identifier can affect the results of the %MIN and %MAX built-in functions. For example, the uppercase and lowercase versions of letters can be equal or unequal depending on the selected sort sequence and language identifier.
Note: The translated field value is used to determine the minimum and maximum, but the untranslated value is returned in the result record.
The example described uses FILEA as an input file. You can also update data using the Open Query File (OPNQRYF) command. However, if you use a mapped field definition to change a field, updates to the field are ignored.
Related reference Built-in functions
These built-in functions are supported for an expression that is used to define a derived field on the MAPFLD parameter or for a complex selection operand specified on the QRYSLT or GRPSLT parameter.
Restricted built-in functions
Some built-in functions are restricted in the way certain relational operators are specified on the QRYSLT and GRPSLT parameters.
Considerations for specifying record formats
Here are the considerations for using the FORMAT parameter on the Open Query File (OPNQRYF) command.
You must specify a record format name on the FORMAT parameter when you request join processing by specifying multiple entries on the FILE parameter (that is, you cannot specify FORMAT(*FILE)). Also, a record format name is normally specified with the grouping function or when you specify a complex expression on the MAPFLD parameter to define a derived field. Consider the following guidelines and rules:
• The record format name is any name you select. It can differ from the format name in the database file you want to query.
• The field names are any names you select. If the field names are unique in the database files you are querying, the system implicitly maps the values for any fields with the same name in a queried file record format (FILE parameter) and in the query result format (FORMAT parameter).
• If the field names are unique, but the attributes differ between the file specified on the FILE parameter and the file specified on the FORMAT parameter, the data is implicitly mapped.
• The correct field attributes must be used when using the MAPFLD parameter to define derived fields.
For example, if you are using the grouping %SUM function, you must define a field that is large enough to contain the total. If not, an arithmetic overflow occurs and an exception is sent to the program.
• Decimal alignment occurs for all field values mapped to the record format identified on the FORMAT parameter. Assume that you have a field in the query result record format with 5 digits with 0 decimals, and the value that was calculated or must be mapped to that field is 0.12345. You will receive a result of 0 in your field because digits to the right of the decimal point are truncated.
Related reference
Example 1: Dynamically joining database files
This example shows how to dynamically join database files without DDS.