Query Subsystem

(Select a Link Definition)

Click this button (in the first column) to access the Select a File Link Definition window for choosing an existing file link definition. The selected definition is loaded into the list and can be adjusted if desired. (File link definitions are defined in File Link Maintenance.)

Alternatively, you can select the Pre-Defined File Links button in the bottom left corner.

Expr

Select this check box if the entry for Link To File/Table is an expression.

Link To File/Table

Name of the link file/table. This can be defined as a fixed value or an expression (see Expr above), which is entered or selected from the drop-down list of existing files/tables.

For PxPlus Data Files

You can select your file from a list of files defined in the NOMADS Data Dictionary, enter the name of a PxPlus View, or enter the name of a physical file path or prefix file reference.

For External Database Files

Your selection must be a table. If the query's main file is a prefix file reference to an external table, then you can use prefix file references instead.

Access Key

Key to be used to read a link file. Select a key from the drop-down list.

Key Expression

PxPlus expression consisting of field(s) from the main file (or other link files) and literals to build the key value to read the link file. A key expression may be string or numeric.

Examples:

     TRN_CUSTID$

     "D"+FLD#2$(1,3)

     PAD(Company$,5,$00$)+PAD(Department$,10,$00$)+STR(EmployeeNum)

     KEY(__fileFN,KNO=0,KEY=Company$:Department$:EmployeeNum)

Multi-Segment Keys

When dealing with multi-segmented keys (i.e. keys made up of more than one field), you can use a + (plus sign) to concatenate multiple string segments of a key when entering a free-form expression. Be sure to add the correct padding logic to the initial key segments. By default, the initial segments of native PxPlus keys are padded with $00$ characters, and the final segment is not padded. Such a key definition might look like this:

     PAD(Company$,5,$00$)+PAD(Department$,10,$00$)+STR(EmployeeNum)

Individual applications can pad key segments with other characters, such as spaces; therefore, knowledge of the key architecture of individual files is a must. Knowledge of segment characteristics is not necessary; however, if the key expression uses the KEY(__linkFN,KNO=n,KEY=FLD1$:FLD2$) format where __linkFN is a special channel variable used by the system when evaluating the key, n is the key number used by the file, and FLD1$:FLD2$ are the key segment variables. The latter format is also more flexible as it can handle changes in key segment length if keys are updated for a file. In addition, this format allows a mixture of string and numeric segments.

Key Expression Builder

An easy way to build a key expression is to use the key builder utility. This is invoked by clicking the dotted button in the cell associated with this option.

The key expression is built by specifying key segments to match the target key definition. These segments can consist of fields from the parent file, literal values and variables. Partial items can be defined, and segments can be padded, have characters stripped, or be converted to upper/lower case.

In the case of multi-segment keys, a Generate a KEY=F1$:F2$ expression check box option is available:

Simple numeric keys can be built using a numeric field. Multi-segment numeric keys should be built using the Generate a KEY=F1$:F2$ expression option.

(The Generate a KEY=F1$:F2$ expression check box was added in PxPlus 2023.)
(Support for numeric key definitions when defining query links was added in PxPlus 2023 Update 1.)

Record Prefix

Prefix to affix to field names to identify the file to which they belong.

Example:

If the CUSTOMER file is given a Record Prefix of CST, then the ID and NAME elements of the CUSTOMER file would be identified as CST.ID$ and CST.NAME$.

Read Record

(Valid Only for Native Files with No Embedded Dictionary)

Select this check box if the file is to be read as an unparsed record.

Connect Options

Connection options for opening an external database table.

Security

Click the dotted button associated with this option to launch the Object Security Definition window for setting up link file level security for the query. See Query Security.

(The Security column was added in PxPlus 2021.)

Pre-Defined File Links

Click this button to access the Select a File Link Definition window for choosing an existing file link definition. The selected definition is loaded into the list and can be adjusted if desired. (File link definitions are defined in File Link Maintenance.)

Alternatively, you can select the link button in the first column of the grid.

(The Pre-Defined File Links button was added in PxPlus 2021 Update 1.)