Query Based file

From PxPlus

A new file *QUERY* has been provided that will allow you to access the contents of a Query as a standard file. This requires a Professional License or the Smart List add-on package

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"

The system will open the query and return the contents of the query in the channel as a memory file. The memory file will have an external key which is derived from the return value of the query and an IOLIST consisting of the column names as specified in the query. For query formulas the system will create a generic column name.

The actual contents of the returned memory file will be a static data set of the returned '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.

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. Examples of bad/invalid data would be non-numerics in numeric fields or formulas that result in an error.

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

For example if you had a query returning 4 fields:

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

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

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

How to avoid bad data

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.

OPT= clause	Description/Function
BADDATA=SKIP	If the BADDATA=SKIP clause is included in the OPEN, the system will skip any records that have any invalid/bad data.
BADDATA=SCAN	If the BADDATA=SCAN clause is included in the OPEN, the system will effectively pre-scan the data and report any errors on the OPEN without opening the file. This option can be used to assure clean data will be returned by the Query.
BADDATA=FLAG	If the BADDATA=FLAG clause is specified the invalid data will be replaced with default values and the record will be returned. The default value for invalid string data is *Error*, for numeric the value will be zero. These default value can be overridden by the BADNUM and BADSTR options below.
BADNUM=value	Defines the value to placed in any numeric field whose data is considered invalid. If not specified the value of zero (0) will be used.
BADSTR=value	Defines the value to place in any string field whose data is considered invalid. If not specified the value of *Error* will be used.