Query Processing

Query Processing

Top  Previous  Next

 

QM verbs that select records from files or produce reports are handled by the query processor. All query processor verbs follow a common format.

 

The query processor verbs are

 

LISTList records meeting specified criteria

 

LIST.ITEMList records meeting specified criteria in internal format

 

LIST.LABELList records meeting specified criteria in address label format

 

REFORMATBuilds a new file from data in the source file

 

SCANSearch for records containing specified text

 

SHOWInteractive select list generation

 

SORTList records meeting specified criteria in order of record id

 

SORT.ITEMList records meeting specified criteria in order of record id in internal format

 

SORT.LABELList records meeting specified criteria in address label format, in order of record id

 

SELECTCreate a select list of records meeting specified criteria

 

SREFORMATBuilds a new file from data in the source file, in order of record id

 

SSELECTCreate a select list of records meeting specified criteria in order of record id

 

SEARCHCreate a select list of records meeting specified criteria which include text matching over the entire record

 

COUNTCount records meeting specified criteria

 

SUMReport total of named fields

 

 

 

The General Form of a Query Processor Verb

 

All query processor verbs follow the same general format though not all parts are applicable to all verbs. The components of the command may be in any order except that the file name must immediately follow the verb and the order may be significant in repeated instances of an element.

 

verb {DICT} file.name

{USING {DICT} file.name}

{field.name {field.qualifier} ...}

{selection.clause }

{sort.clause }

{display.clause }

{record.id...}

{FROM select.list.no}

{TO select.list.no}

 

where

 

verbis the query processor verb name

 

{DICT} file.nameidentifies the file to be processed. The optional DICT keyword indicates that the dictionary part of the file is to be processed. The DICT.DICT file will be used as the dictionary defining the items in the dictionary being reported.

 

USING {DICT} file.nameindicates that a dictionary other than the one normally associated with the file is to be used.

 

field.nameis the name of a field (D or I-type) to be displayed. Multiple fields may be specified in a single command. In addition, the special construct Fn or An may be used to reference field n and the EVAL keyword may be used to introduce an evaluated expression. Similarly, the ELEMENT keyword may be used to reference a data collection element by element path.

 

field.qualifierprovides qualifying information about the immediately preceding field.name such as the format in which it is to be displayed.

 

selection.clausespecifies criteria determining which records from the file are to be included.

 

sort.clausespecifies the order in which records are to be processed.

 

display.clausecontrols the manner in which data is displayed or printed.

 

record.idspecifies a particular record id is to be processed. Multiple record ids may be specified.

 

FROM select.list.nospecifies that a select list is to be used to control which records are processed. If the FROM option is not used and the default select list (list 0) is active, this list will be used automatically. If used in conjunction with one or more record.id items, only records that appear in the select list and are named record.ids will be processed.

 

TO select.list.nofor verbs that produce a select list, specifies which list is to be created. If the TO option is not used, the default select list (list 0) is used.

 

Phrases defined in the VOC or the dictionary may be included at any point in a query processor command and will be expanded at that position in the command line.

 

Literal values used in selection or sort clauses do not need to be enclosed in quotes unless they correspond to names defined in either the VOC or the file's dictionary or if the contain spaces, commas or quotes. Use of quotes is recommended to prevent incorrect interpretation of commands.

 

 

 

The $QUERY.DEFAULTS Record

 

The default actions of the query processor can be controlled by adding an X-type record to the VOC file or to the dictionary of the file referenced by the query command.

Field 1X
Field 2Query processor command line elements that will be inserted into a LIST, SORT, LIST.LABEL or SORT.LABEL command after the file name but before any further command line options.
Field 3Query processor command line elements that will be inserted into a SELECT, SSELECT or SEARCH command.
Field 4Format code to be applied to the automatically inserted column for the record id if there is no @ID definition in the dictionary. If not present, "12L" is used by default. Only simple width and justification format codes are allowed.

 

In all cases, the options may extend over multiple lines by use of an underscore as the last character of the line to indicate that a continuation line is present. The lines are merged together with the underscore replaced by a single space. The field numbers described above are applied to the record after merging continuation lines.

 

The query processor checks first for this optional record in the dictionary of the file. If it is not found or there is no dictionary, it then looks in the VOC. It is therefore possible to use a VOC record to set account level defaults which can be overridden by an alternative record in individual dictionaries. A $QUERY.DEFAULTS record in the dictionary with a type code of X but no further content will effectively disable use of the VOC $QUERY.DEFAULTS record whilst not applying any defaults of its own.

 

 

 

Links

 

Dictionary L-type records can be used to represent a relationship between two files without the need to include a separate I-type TRANS() expression for each field.

 

In a query command, a link is used by specifying a field name that is constructed from the link name and the name of a field in the linked file, separated by a percent sign (%).

 

For example, consider a library application where the BOOKS file representing a physical copy of a book uses a composite key constructed from the id of a record in the TITLES file and the copy number, separated by a hyphen. A link record could be placed in the dictionary of the BOOKS file:

 

TITLES1: L
2: @ID['-', 1, 1]
3: TITLES

 

A query against the BOOKS file may then refer to fields from the TITLES file as, for example, TITLES%AUTHOR. The linked field (AUTHOR in this example) may be an A, C, D, I or S-type item.

 

To allow use of field names that contain % characters, the query processor only interprets a field name containing a % character as a link if there is no dictionary or VOC item corresponding to the entire name.

 

Data items can also be referenced in I-type expressions via links. For example,

OCONV(TITLES%AUTHOR, "MCT")

 

 

Report Styles

 

A style definition sets visual attributes that highlight specific parts of a report such as headings, subtotals and totals. When the report is directed to the screen, the visual attribute settings enable use of colour. For reports sent to a PCL printer, font weight can be set in style definition.

 

The style definition appears in an X-type record in either the dictionary or the VOC. Details of how the style definition operates can be found with the description of the STYLE query processor keyword.