Special File Handling 

Format

OPEN (chan[,fileopt]) "*QUERY*;query;library[;option] [...]"

Where:

Description

The *QUERY* interface allows a NOMADS query to be used to define the contents of a logical file for use by an application program. It allows the program to access the contents of a query as if it was a standard read-only file.

(The *QUERY* logical file name was added in PxPlus v9.00.)

Opening the Query

To open the contents of a query, specify the file name *QUERY* followed by the panel and library name for the query you wish to access, separated by ; (semi-colons).

open (1)"*QUERY*;CustQry;mylib.en"

When opened, the system will process the query definition and return the contents of the query in the channel number specified as a memory file.

By default, the memory file will have an external key, which is derived from the return value of the query or from the "KNO=n" option specified in the OPT= clause. See File Parameters.

If neither is specified, then the value in the first column of the query will be used as the record key.

If there is a possibility of duplicate keys, data can be lost when the records with duplicate keys are overwritten. In this case, it is recommended that you use the SEQPFX=Y clause, which prefixes the key with a 10-digit sequence number to guarantee unique keys and no data loss. See File Parameters.

(The SEQPFX=Y file option was added in PxPlus 2021.)

To load the file that will be returned by *QUERY*, the data is read using the key specified in the query definition. It is then written to the *memory* file using the key as derived above. If neither "KNO=n" nor SEQPFX=Y are specified as file options, the resulting data records will be returned in ascending order based on the derived key value, and not necessarily based on the original order read.

If the SEQPFX=Y file option is specified, then the resulting data records will be returned in the order read, maintaining ascending/descending key segment sequence, and key values returned by a key function (i.e. KEY( ), KEC( ), KEF( ), KEL( ), etc.) will include the 10-digit sequence number.

If the "KNO=n" option is specified but not SEQPFX=Y, and the key by which the *QUERY* file is read has a descending segment, the key value returned by a key function will contain an altered value in the location of the descending segment to maintain the descending sequence. To revert the key back to an unaltered value, it can be manipulated using the %_KeyMask$ variable, which is set when *QUERY* is opened:

k$=kec(channel);
TrueKeyValue$=xor(k$,%_KeyMask$(1,len(k$)))

(Support for descending keys was added in PxPlus 2019 Update 2.)

The file will also have an IOList consisting of the column names as specified in the query, with the exception of array elements and formulas. Array elements will consist of the column name followed by __s1.s2.s3 where s1, s2 and s3 are subscript numbers for up to three dimensions (with .s2 and .s3 being optional); e.g. a variable such as MyArray$[2,3] will become MyArray__2.3$.

(Support for array elements in the query was added in PxPlus 2022.)

For query formulas, the system will use the formula name assigned in the query definition. If none is assigned, the column will be assigned a generic column name (COL__nnn).

The actual contents of the returned memory file will be a static data set of the column values for the specified query; that is, the file returned will be a "snapshot" of the query results as of the time the file was opened. No updates or changes to the original data sources will be reflected in the file as your application is processing it.

(The ability to assign a formula name in the query definition was added in PxPlus 2020.)

Closing the channel will free the memory file and free its contents.

Note:
The file returned will always have an internal IOList.

Handling Bad Data

By default, should the query report any columns with invalid/bad data when the data is READ, the value for invalid string data will be *Error*; for numeric, the value will be zero. Examples of bad/invalid data would be non-numerics in numeric fields or formulas that result in an error such as divide by zero.

Like normal files, the actual column with the bad data will terminate the READ command; however, you can skip fields that may return bad data by placing an * (asterisk) in the IOList definition, thereby causing the system not to return that field.

Example:

If you had a query returning four fields, the normal READ would look like:

read (fileno)Client$,Name$,DateDue$,SalePerson$

If there was a problem with the data and the Date Due field had a data error in it (perhaps it was a formula derived column), you could skip this field as follows:

read (fileno)Client$,Name$,*,SalePerson$

Obviously, this can only be done if not using the internal IOList provided with the query file. If you are processing queries where bad data is likely to occur, you can specify an OPT= clause that defines what action to take in the case of bad data. See File Parameters below.

File Parameters

The following parameters are available for use in the OPT= clause:

Example:

open (channel,opt="BADDATA=SKIP;KNO=1;SEQPFX=Y")"*QUERY*;CUSTQRY;scrnlib.en"