Home News Version 1 Tech Support Pubs S2K Site Map

SYSTEM 2000 Quick Reference Guide QUEST Language Commands

ADD
Ad Hoc Function
ASSIGN
ASSIGN TREE
By-Clause
CHANGE value
CLEAR
COLLECT
COPY TREE
DATE FORMAT IS
DESCRIBE
DITTO
ENABLE/DISABLE EXECUTION
ENABLE/DISABLE VALUES PADDING
FORMOP
FRAME and END FRAME
INSERT TREE
LIMIT and END LIMIT
LIST
LOAD
MOVE TREE
Ordering-Clause
PRINT
QUEUE
RELOAD
REMOVE
REMOVE TREE
REPORT
Retrieval-Clause
SAME IS
STOP IF/CONTINUE IF
System Function
System String
TALLY
UNLOAD
Where-Clause

ADD

Replaces nulls with values in existing data records.

Syntax

ADD item full-trace EQ |value *  :
                   |PREVIOUS

ADD item full-trace] = |item                   :
                   |* function *
                   |* function (parameters)
                   |(adhoc-function)
                   |system-function

ADD item [partial-trace] EQ |value * where-clause :
                        |PREVIOUS

ADD item [partial-trace] = |item           where-clause :
                       |* function *
                       |* function (parameters)
                       |(adhoc-function)
                       |system-function

ADD record full-trace EQ |value-stream END * :
                     |PREVIOUS

ADD record [partial-trace] EQ |value-stream END * where-clause:
                          |PREVIOUS

item: is a schema item name or component number.
full-trace: is a list of one or more positive integers or 0, each preceded by the system separator (for example, *1*4*0). In a left-to-right scan, each integer designates the position of the child record under its parent at each level. The rightmost integer designates the level 0 position. Zero means last.
value: is a literal value or a system string (*NOW*, *TODAY*, *FTODAY*).
PREVIOUS: repeats the value stream or the item value in the preceding update command.
function: is a stored function name or component number.
parameters: are the values that replace the numbered parameters in the stored function. See Function Definition in DEFINE.
adhoc-function: is a mathematical expression enclosed in parentheses. See Ad Hoc Function.
system-function: allows you to obtain simple arithmetic statistics about values stored in a database. System functions include: AVG, COUNT, MAX, MIN, SIGMA, and SUM. See System Function.
partial-trace: is a trace in which the last integer designates a position other than level 0.
record: is a schema record name or component number.
value-stream: is a series of item numbers and values to be added, with each item number followed by its associated value. The system separator must follow each item number and each value. The item numbers do not have to be in DESCRIBE order.
where-clause: selects specific data records. See Where-Clause.

Examples

AD C412 EQ BS* WH C1 = 1005:

ADD EDUCATION EQ 413*01/07/85*414*ENGLISH*415*MARKETING*END*
   WH C2 EQ BROOKS:

ADD C126 = (C124 - C114) WH C1 EXISTS AND C121 EQ 06/30/91:

ADD C105 = (*TODAY*) WH C2 = GIBSON:

Rules

Abbreviation: ADD = AD
Use ADD only to add data to existing data records that are occurrences of a single schema record.
If you need to add multiple values, all values must be occurrences of items in the specified record.
If you use the syntax 'ADD item = item', the item types must be compatible, that is, both dates, both numeric, or both nonnumeric. For numeric types, the second item assumes the picture of the first item. Also, if the value of the second item is too large for the picture of the first item, an error message appears. If the item name for either includes the equals sign, use the component number for the item.
Also for the 'ADD item = item' syntax, if one item is type UNDEFINED, the other item must also be type UNDEFINED. That is, you can add only UNDEFINED values to other UNDEFINED items. You cannot add CHARACTER or TEXT values to UNDEFINED items.
If you use the '=' syntax, the item, stored function, system function, or ad hoc function must be in the path of the target record (the record containing the specified item). If the system function includes a by-phrase, the record specified in the by-phrase must be in the path of the target record.
You must have U-authority for components specified in the action-clause of an ADD command.
The command referenced by PREVIOUS cannot contain errors.
When specifying PREVIOUS, the item or record preceding EQ must be the same item or record as in the previous update command.
You cannot use Collect File items in an ADD action-clause.
You cannot issue ADD for a damaged database.

Ad Hoc Function

Specifies mathematical operations to be performed on data. The following operands are available:

Syntax

*         multiplication

/         division

+         addition

-         subtraction

Examples

PRINT (C114 + C126) WHERE C1 EQ 1010:

PR (((MIN C126) + (MAX C126))/2):

Rules

You can specify an ad hoc function in the action-clause of a retrieval or update command or in a by-clause.
An ad hoc function must be enclosed in parentheses. Parentheses control the order of operations; the innermost pair of parentheses encloses the first operation performed.
An ad hoc function can contain system functions (enclosed in parentheses), the system string *FTODAY*, stored functions, numeric data items (in the same path), constants, and dates. Specify dates in the form MM.DD.YY to avoid confusing the slashes with the division operand.
Multiplication and division operations are performed before addition and subtraction operations
If both multiplication and division operations are specified, the software does the calculations on the left and works toward the right.
You can nest ad hoc functions.
You can use E-notation for constants.
If you include more than one item type in an ad hoc function, the software determines the output format by the highest item type, going from highest to lowest in this order: DOUBLE, REAL, DECIMAL, MONEY, INTEGER, and DATE.
You can store an ad hoc function in the definition by assigning a component name, number, and type. See Function Definition in DEFINE.
You can include Collect File items in an ad hoc function with retrieval commands but not with update commands.
You can include component names in an ad hoc function as long as they do not include slashes, plus signs, or minus signs.
If one or more values for specified items are null, no output is produced unless the NULL or ZERO format option is in effect. If NULL is set, the output for the missing values is -NULL-. If ZERO is set, the missing values are replaced with zeros, and the calculations are made and displayed. Nulls for dates are not converted to zero.

ASSIGN

Replaces values or nulls with values in existing data records.

Syntax

ASSIGN item full-trace EQ |value *  :
                      |PREVIOUS

ASSIGN item full-trace = |item                    :
                     |* function *
                     |* function (parameters)
                     |(adhoc-function)
                     |system-function

ASSIGN item [partial-trace] EQ |value * where-clause :
                           |PREVIOUS

ASSIGN item [partial-trace] = |item                where-clause :
                          |* function *
                          |* function (parameters)
                          |(adhoc-function)
                          |system-function

ASSIGN record full-trace EQ |value-stream END * :
                        |PREVIOUS

ASSIGN record [partial-trace] EQ |value-stream END * where-clause :
                             |PREVIOUS

item: is a schema item name or component number.
full-trace: is a list of one or more positive integers or 0, each preceded by the system separator (for example, *1*4*0). In a left-to-right scan, each integer designates the position of the child record under its parent at each level. The rightmost integer designates the level 0 position. Zero means last.
value: is a literal value or a system string (*NOW*, *TODAY*, *FTODAY*).
PREVIOUS: repeats the value stream or the item value in the preceding update command.
function: is a stored function name or component number.
parameters: are the values that replace the numbered parameters in the stored function. See Function Definition in DEFINE.
adhoc-function: is a mathematical expression enclosed in parentheses. See Ad Hoc Function.
system-function: allows you to obtain simple arithmetic statistics about values stored in a database. System functions include: AVG, COUNT, MAX, MIN, SIGMA, and SUM. See System Function.
partial-trace: is a trace in which the rightmost integer designates a position other than level 0.
record: is a schema record name or component number.
value-stream: is a series of item numbers and values to be assigned, with each item number followed by its associated value. The system separator must follow each item number and each value. The item numbers do not have to be in DESCRIBE order.
where-clause: selects specific data records. See Where-Clause.

Examples

AS OFFICE-EXTENSION EQ 410 XT369* WH C1 = 1120:

AS C14 EQ 2311 HANSFORD* WH C3 EQ MOLLY I. AND C2 EQ GIBSON:

ASSIGN JOB SKILLS EQ 201*RUSSIAN*202*EXCELLENT*203*15*END*
   WH C2 EQ BOWMAN:

Rules

Abbreviation: ASSIGN = AS
Use ASSIGN only to replace data in existing data records that are occurrences of a single schema record.
If you specify a record and if any original data are to be retained, those data must be included as a part of the value stream.
If you need to assign multiple values, all values must be occurrences of items in the specified record.
If you use the syntax 'ASSIGN item = item', the item types must be compatible, that is, both dates, both numeric, or both nonnumeric. For numeric types, the second item assumes the picture of the first item. Also, if the value of the second item is too large for the picture of the first item, an error message appears. If the item name for either includes the equals sign, use the component number for the item.
Also for the 'ASSIGN item = item' syntax, if one item is type UNDEFINED, the other item must also be type UNDEFINED. That is, you can assign only UNDEFINED values to other UNDEFINED items. You cannot assign CHARACTER or TEXT values to UNDEFINED items.
If you use the '=' syntax, the item, stored function, system function, or ad hoc function must be in the path of the target record (the record containing the specified item). If the system function includes a by-phrase, the record specified in the by-phrase must be in the path of the target record.
You must have U-authority for components specified in the action-clause of an ASSIGN command.
The command referenced by PREVIOUS cannot contain errors.
When specifying PREVIOUS, the item or record preceding EQ must be the same item or record as in the previous update command.
You cannot use Collect File items in an ASSIGN action-clause.
You cannot issue ASSIGN for a damaged database.

ASSIGN TREE

Replaces existing data trees (including the data) with new data trees.

Syntax

ASSIGN TREE record full-trace EQ |value-stream END* :
                             |PREVIOUS

ASSIGN TREE record [partial-trace] EQ |value-stream END* where-clause :
                                  |PREVIOUS

record: is a schema record name or component number; the target record.
full-trace: is a list of one or more positive integers or 0, each preceded by the system separator (for example, *1*4*0). In a left-to-right scan, each integer designates the position of the child record under its parent at each level. The rightmost integer designates the level 0 position. Zero means last.
value-stream: is a series of component numbers and associated item values in the same format as for the loader stream. The record number to the left of the EQ operator is not included in the value stream. See LOAD for the format of a loader stream.
PREVIOUS: repeats the value stream or the item value in the preceding update command.
partial-trace: is a trace in which the rightmost integer designates a position other than level 0.
where-clause: selects specific data records. See Where-Clause.

Example

AT C100 EQ 101*PROGRAMMER*102*INFORMATION SYSTEMS*103*MYJ*104*
   PROFESSIONAL*105**TODAY**110*111*1100.00*112*MONTHLY*113*
   *TODAY**114*194.50*120*120*130*131*1.0*132*NEW EMPLOYEE*END*
   WH C1 = 1345:

Rules

Abbreviation: ASSIGN TREE = AT
If any of the original data are to be retained, they must be included as part of the value stream. Otherwise, the data will be removed from the database.
If a value stream is not included, the selected record and its descendants are replaced by an empty record without descendants.
You must have U-authority for components specified in an ASSIGN TREE command.
The command referenced by PREVIOUS cannot contain errors.
When specifying PREVIOUS, the item or record preceding EQ must be the same item or record as in the previous update command.
You cannot use Collect File items in an ASSIGN TREE action-clause.
You cannot issue ASSIGN TREE for a damaged database.

By-Clause

Specifies that only certain items within a data tree are to be displayed together in logical order. Enables you to specify which values are to be retrieved, to retrieve data from disjoint records, or to specify computations on subsets of data.

Syntax

by-phrase, units

unit by-phrase

by-phrase

BY |record
   |DATA BASE

unit

|item
|record
|* function *
|* function (parameters)
|* string *
|* string (parameters)
|(adhoc-function)
|system-function
|cf-item

item

is a schema item name or component number.

record

is a schema record name or component number.

function

is a stored function name or component number.

string

is a stored string name or component number.

parameters

are the values that replace the numbered parameters in the stored function. See Function Definition in DEFINE.

adhoc-function

is a mathematical expression enclosed in parentheses. See Ad Hoc Function.

system-function

allows you to obtain simple arithmetic statistics about values stored in a database. System functions include: AVG, COUNT, MAX, MIN, SIGMA, and SUM. See System Function.

cf-item

is a Collect File item name.

Examples

LIST C1, C2, BY C100, C102, C132 WH C7 EQ FEMALE:

LIST C1, C2, BY C100, C102, C132, BY C110, C121 WH C7 = FEMALE:

PRINT BY C0, C102, C201 WH C7 EQ FEMALE:

PRINT COUNT C0, BY DATA BASE, COUNT C0 WHERE C7 = FEMALE:

PRINT C111 BY C0 WHERE SAME:

PRINT BY C0, C111 BY C100 WHERE SAME:

Rules

In a by-phrase, BY DATA BASE qualifies the entire database for processing.
Only numeric item types can be used with the AVG, SIGMA, and SUM system functions.
You can use schema records with the COUNT system function, but you cannot use schema records with other system functions.
The retrieval objects in a by-clause must be in the tree topped by the record specified in the by-phrase.

CHANGE value

Replaces existing values with new values.

Syntax

CHANGE item full-trace EQ |value *  :
                      |PREVIOUS

CHANGE item full-trace = |item                    :
                     |* function *
                     |* function (parameters)
                     |(adhoc-function)
                     |system-function

CHANGE item [partial-trace] EQ |value * where-clause :
                           |PREVIOUS

CHANGE item [partial-trace] = |item                 where-clause :
                          |* function *
                          |* function (parameters)
                          |(adhoc-function)
                          |system-function

CHANGE record full-trace EQ |value-stream END * :
                        |PREVIOUS

CHANGE record [partial-trace] EQ |value-stream END * where-clause :
                             |PREVIOUS

item: is a schema item name or component number.
full-trace: is a list of one or more positive integers or 0, each preceded by the system separator (for example, *1*4*0). In a left-to-right scan, each integer designates the position of the child record under its parent at each level. The rightmost integer designates the level 0 position. Zero means last.
value: is a literal value or a system string (*NOW*, *TODAY*, *FTODAY*).
PREVIOUS: repeats the value stream or the item value in the preceding update command.
function: is a stored function name or component number.
parameters: are the values that replace the numbered parameters in the stored function. See Function Definition in DEFINE.
adhoc-function: is a mathematical expression enclosed in parentheses. See Ad Hoc Function.
system-function: allows you to obtain simple arithmetic statistics about values stored in a database. System functions include: AVG, COUNT, MAX, MIN, SIGMA, and SUM. See System Function.
partial-trace: is a trace in which the rightmost integer designates a position other than level 0.
record: is a schema record name or component number.
value-stream: is a series of values to be changed, each preceded by its associated item number. The system separator must follow each item number and each value. The item numbers do not have to be in DESCRIBE order.
where-clause: selects specific data records. See Where-Clause.

Examples

CH PAY SCHEDULE EQ MONTH*  WH C112 EQ MONTHLY:

CHANGE C11 EQ 80.00* WHERE C1 EQ 1120:

CHANGE C131 = (C131/10) WH C1 = 1043:

CH JOB SKILLS EQ 202*GOOD*203*3*END* WH C201 EQ GRAPHICS AND
   C1 EQ 1043:

CH C0 EQ 14* 1302 LAZY LANE* 16* 78752* END* WH C1 = 1120:

Rules

Abbreviation: CHANGE = CH
Use CHANGE only to change data within existing data records that are occurrences of a single schema record.
If you need to change multiple values, all values must be occurrences of items in the specified record.
If you use the syntax 'CHANGE item = item', the item types must be compatible, that is, both dates, both numeric, or both nonnumeric. For numeric types, the second item assumes the picture of the first item. Also, if the value of the second item is too large for the picture of the first item, an error message appears. If the item name for either includes the equals sign, use the component number for the item.
Also for the 'CHANGE item = item' syntax, if one item is type UNDEFINED, the other item must also be type UNDEFINED. That is, you can change only UNDEFINED values to other UNDEFINED items. You cannot change CHARACTER or TEXT values to UNDEFINED items.
If you use the '=' syntax, the item, stored function, system function, or ad hoc function must be in the path of the target record (the record containing the specified item). If the system function includes a by-phrase, the record specified in the by-phrase must be in the path of the target record.
You must have U-authority for components specified in the action-clause of a CHANGE command.
The command referenced by PREVIOUS cannot contain errors.
When specifying PREVIOUS, the item or record preceding EQ must be the same item or record as in the previous update command.
You cannot use Collect File items in a CHANGE action-clause.
You cannot issue CHANGE for a damaged database.

CLEAR

Specifies when modified database pages, or Update Log pages, or both are to be written from main memory to disk.

Syntax

CLEAR :

[CLEAR] AUTOMATICALLY :

CLEAR UPDATE LOG :

[CLEAR] UPDATE LOG AUTOMATICALLY :

Examples

CLEAR UPDATE LOG AUTO:

CLEAR:

CLEAR UPDATE LOG:

Rules

Abbreviation: AUTOMATICALLY = AUTO
If you do not issue a CLEAR command, clearing is done by the buffer manager when space occupied by the updated database pages, Update Log pages, or both needs to be made available. Or, clearing is done by the software when certain commands are issued.
When you issue CLEAR AUTOMATICALLY, the software clears all modified database pages and continues to do so after each update operation.
When you issue CLEAR UPDATE LOG AUTOMATICALLY, the software clears all Update Log buffers and continues to do so after each update operation.

COLLECT

Creates (or cancels) a Collect File, which is a relational table containing values extracted from one or more databases.

Syntax

COLLECT [/[options] [,TITLE comment]/] retrieval-clause
     [[,ordering-clause] where-clause] :

COLLECT :

options

is a list of one or more of the following format options, separated by commas. Defaults are listed in the first column.

INDENT               BLOCK
NULL SUPPRESS        NULL
NUMBER               NAME
REPEAT               REPEAT SUPPRESS
SINGLE SPACE         DOUBLE SPACE
STUB                 STUB SUPPRESS
TREE                 RECORD
ZERO SUPPRESS        ZERO
HEX OFF              HEX ON

See FORMOP for details on format options.

TITLE

allows you to insert a comment within the output. (If you do not specify format options, do not include a comma before the keyword TITLE.)

comment

can be any string of characters not containing the command terminator or a slash. The comment cannot begin with (, B(, D(, F(, L(, R(, or S(, and it must be less than 130 characters long, although leading, trailing, and extraneous blanks are not counted. The comment is displayed with the echo of the command in the Message File.

retrieval-clause

cf-retrieval-object [,cf-retrieval-object]

cf-retrieval-object

[cf-item = ] retrieval-object

cf-item

is a unique Collect File item name, which can be any character string. If reserved words or component numbers are used, the Collect File cannot be used to create a new database. The Collect File item name can be omitted if it is the same as the retrieval object's name.

retrieval-object

is a schema item name or component number, Collect File item name, stored function, system function, or ad hoc function. You cannot specify a schema record unless it is embedded in a system function.

ordering-clause

specifies the order of selected data records. A where-clause is required if an ordering-clause is specified. See Ordering-Clause.

where-clause

selects specific data records. See Where-Clause.

Examples

COLLECT LAST NAME, HIRE DATE, ACCRUED VACATION:

COLLECT:

CO NAME = LAST NAME, HIRED = HIRE DATE, VACATION = ACCRUED VACATION,
   OB HIRE DATE WH HIRE DATE SPANS 01/01/1988 * 01/01/1989:

Rules

Abbreviation: COLLECT = CO
To cancel a Collect File, issue COLLECT without other syntax, or end the session.
You can display Collect File items with the DESCRIBE command.
You can specify Collect File items in the retrieval-clause of PRINT, LIST, UNLOAD, and COLLECT commands, and in any where-clause except with MAP and RELOAD.
If the retrieval-clause specifies items in disjoint records and if a where-clause is specified, the retrieval-clause must contain a by-phrase.
You cannot specify a schema record as a Collect File item.
Each Collect File item name must be unique.
A maximum of 59 Collect File items can be collected in a Collect File record.
If you specify an ad hoc function in a COLLECT command, the Collect File item type will be the type of the last item (or constant) in the function. For example, if C1 is a DATE item type, COLLECT (C1 + 20) produces an INTEGER Collect File item whose values equal the elapsed days in the C1 values plus 20. However, COLLECT (20 + C1) produces a DATE Collect File item whose values are the C1 dates plus 20 days.

COPY TREE

Duplicates a data record and its descendants from one position in a database to another.

Syntax

COPY TREE record |where-clause               TO |before-clause              :
                |partial-trace where-clause        |after-clause
                |full-trace                     |partial-trace where-clause
                                              |full-trace

record: is the name or component number of the schema record at the top node of the tree to be copied.
where-clause: selects specific data records. See Where-Clause.
partial-trace: is a trace in which the rightmost integer designates a position other than level 0.
full-trace: is a list of one or more positive integers or 0, each preceded by the system separator (for example, *1*4*0). In a left-to-right scan, each integer designates the position of the child record under its parent at each level. The rightmost integer designates the level 0 position. Zero means last.
before-clause: is the same as a where-clause except that the keyword BEFORE replaces the keyword WHERE. See Where-Clause.
after-clause: is the same as a where-clause except that the keyword AFTER replaces the keyword WHERE. See Where-Clause.

Examples

COPY TREE C110 WHERE C113 = 03/01/90 AND C1 = 1120
   TO AFTER C113 EQ 03/01/91 AND C1 EQ 1120:

CT C0*1 TO *3:

Rules

Abbreviations:

     AFTER = AF
     BEFORE = BE
     COPY TREE = CT

You must have U-authority for components specified in a COPY TREE command.
The copy operation requires a one-to-one relationship. That is, a data tree is copied from its old position to a single new point of attachment. It cannot be copied to multiple new positions.
The data tree to be copied can be an entire logical entry or a subtree.
Copying a data tree changes the Hierarchical Table and the Data Table. The index tables are also changed if the data tree copied contains key values.
Although you cannot specify format options with COPY TREE, this command honors the TREE/RECORD format option in effect. That is, if RECORD is specified in a previous command, COPY TREE duplicates only the top node of the selected data tree.
You can use SAME in either or both where-clauses. The DYNAMIC and STATIC settings are honored in the same way as any other consecutive where-clauses. Therefore, the "from" where-clause can affect the "to" where-clause, depending on which setting is used for SAME. After a COPY TREE completes processing, the SAME file contains the results of the specified "to" where-clause if SAME is set to DYNAMIC.
You cannot issue COPY TREE for a damaged database.
If Security by Entry is in effect for the database, you can use a secondary password to copy a subtree from one logical entry to another, provided you know the entry key values for both logical entries. The last where-clause used in setting up the "to" position determines the current entry key after the COPY TREE command completes processing.

DATE FORMAT IS

Specifies alternate input and output formats for dates.

Syntax

DATE FORMAT IS |MM/DD/YYYY :
               |MM/DD/YY
               |DD/MM/YYYY
               |DD/MM/YY
               |YY/MM/DD
               |YYYY/MM/DD

Example

DATE FORMAT IS MM/DD/YY:

Rules

At the beginning of each session, the date format is set to MM/DD/YYYY.
Issuing this command changes the date format for any database you open during the session.
This command changes the date format for your session only. Other users of the database are not affected.

DESCRIBE

Displays all or part of a database definition.

Syntax

DESCRIBE :

DESCRIBE |[/DEFINE/]             |component [THROUGH component] :
         |[/AUTHORITIES [n]/]    |RECORD unit
         |[/DEFINE,AUTHORITIES/]

DESCRIBE [/DEFINE/] |STRINGS   :
                    |FUNCTIONS

DESCRIBE /COLLECT FILE [,DEFINE]/ [cf-item [(CF)] [THROUGH cf-item [(CF)]]] :

component: is an item, record, string, or function name or component number.
n: is a positive integer indicating the column in which component authorities are to be printed. The number must be between one and the line length, minus three. The default is 68.
unit: is the component name or number of a schema record or schema item for the RECORD option. If you specify an item, the software displays the parent schema record along with its items and subordinate records.
cf-item: is a Collect File item name.

Examples

DESCRIBE:

DESCRIBE /DEFINE/ SECURITY CLEARANCE:

DESC C110:

DESCRIBE /CF/ C13 (CF):

DESCRIBE C400 THRU C415:

DESCRIBE STRINGS:

DESCRIBE FUNCTIONS:

DESCRIBE /AUTHORITIES 45/:

DESCRIBE /AUTH 50/C100 THROUGH C110:

DESCRIBE RECORD C100:

DESCRIBE /AUTH 41/ RECORD C100:

DESCRIBE /DEFINE/ RECORD C100:

DESCRIBE /DEFINE, AUTH/ C0 THRU C10:

Rules

Abbreviations:

     AUTHORITIES = AUTH
     COLLECT FILE = CF
     DESCRIBE = DESC
     THROUGH = THRU

DESCRIBE can be issued by the master password holder or a secondary password holder. If the DBA password holder issues DESCRIBE, the software displays header information only.
If a command specifies THROUGH (for example, DESCRIBE C400 THROUGH C415:), the first component name or number must precede the second component name or number in the database definition.
If the component name consists of several words and one of them is either THROUGH or THRU (for example, THROUGH STREETS), specify the component number instead of the component name. This rule does not apply to component names such as THROUGHPUT or THRUWAY, but it does apply to THROUGH-STREETS or THRU-WAY.
The software displays Collect File items only if you specify the /COLLECT FILE/ option. Use the optional (CF) syntax if the Collect File item name is the same as a component in the database.
Do not mix database components and Collect File items in this command.
You cannot specify the /AUTHORITIES/ option with the /COLLECT FILE/ option or when describing strings or functions.
To display component C0 (ENTRY), you must change ENTRY to another name.
You can enter the /DEFINE,AUTHORITIES/ option as /AUTHORITIES,DEFINE/.
For DESCRIBE RECORD, the output includes descriptions of schema records that are part of the specified record but not the items in those subordinate records.

DITTO

Repeats the most recently executed action-clause of a retrieval or update command.

Syntax

DITTO [where-clause] :

where-clause: selects specific data records. See Where-Clause.

Example

PR COUNT ENTRY WH C7 EQ FEMALE AND C124 GE $1000.00 AT 1:
   .
   .
   .
DI WH GENDER EQ MALE AND C124 GE $1000.00 AT 1:

Rules

Abbreviation: DITTO = DI
You can use DITTO after the following commands: ADD, ASSIGN, ASSIGN TREE, CHANGE, COLLECT, DESCRIBE, INSERT TREE, LIST, PRINT, REMOVE, REMOVE TREE, TALLY, UNLOAD.
If the previous retrieval or update command does not contain a where-clause, the DITTO command must not contain one. However, if the previous retrieval or update command contains a where-clause, the DITTO command must contain one.
The keyword identifying the where-clause (WHERE, BEFORE, AFTER) must be the same in the DITTO command as in the previous retrieval or update command.
If the action-clause in the previous retrieval or update command contains an ordering-clause, it is repeated by DITTO. However, if the action-clause in the previous retrieval or update command does not contain an ordering-clause, one cannot be added with DITTO.

ENABLE/DISABLE EXECUTION

Allows or prevents the execution of QUEST, DEFINE, or QUEUE retrieval and update commands. However, ENABLE EXECUTION and DISABLE EXECUTION allow scanning of those commands for syntax errors.

Syntax

|ENABLE  EXECUTION :
|DISABLE

Examples

ENABLE EXECUTION:

DISABLE EXECUTION:

Rules

At the beginning of each session, execution is enabled.
You can issue this command from the DEFINE, QUEST, or QUEUE processor. Commands in all three processors are affected, regardless of which processor ENABLE/DISABLE EXECUTION is issued from.

ENABLE/DISABLE VALUES PADDING

Controls the placing of new values of key items in the Distinct Values Table.

Syntax

|ENABLE  VALUES PADDING :
|DISABLE

Example

ENABLE VALUES PADDING:

Rules

Only the master password holder or the DBA password holder (with authorization) can issue this command.
The default is disabled values padding. Once ENABLE VALUES PADDING is issued, it remains in effect until DISABLE VALUES PADDING is issued.
Enable values padding before doing massive loading with the LOAD command or with a PLEX program. After the load processing completes, you should disable values padding or too much table space will be added during ad hoc updates.
ENABLE VALUES PADDING has no effect on the indexes for the items defined WITH MANY FUTURE OCCURRENCES.

FORMOP

Sets format options without your having to issue a PRINT, LIST, UNLOAD, or COLLECT command.

Syntax

FORMOP /options/ :

options

is a list of one or more of the following format options, separated by commas. Defaults are listed in the first column.

INDENT               BLOCK
NULL SUPPRESS        NULL
NUMBER               NAME
REPEAT               REPEAT SUPPRESS
SINGLE SPACE         DOUBLE SPACE
STUB                 STUB SUPPRESS
TREE                 RECORD
ZERO SUPPRESS        ZERO
HEX OFF              HEX ON

The rules pertaining to the format options also apply when you specify format options with the PRINT, LIST, UNLOAD, or COLLECT command.

Example

FORMOP /REPEAT SUPPRESS, DOUBLE SPACE/:

Rules

Abbreviations:

     NUMBER = NUM, NUMB
     SUPPRESS = SUP

This command changes the format options until the end of the session or until you issue another FORMOP, PRINT, LIST, UNLOAD, or COLLECT command specifying new format options.
If conflicting format options are specified, the one specified last will be in effect.
INDENT/BLOCK controls the indentation of lower level items.
SINGLE SPACE/DOUBLE SPACE controls vertical spacing of output.
Specifying DOUBLE SPACE in an UNLOAD command causes errors because of unwanted blanks between lines.
STUB/STUB SUPPRESS controls the display of item labels. If STUB is in effect, NUMBER/NAME affects whether a component number or name precedes a value. STUB SUPPRESS eliminates the name or number, as well as the default column headings in a LIST command.
NUMBER/NAME specifies that the item name or number is to be displayed when a PRINT command is issued and STUB is in effect. If STUB SUPPRESS is in effect, NUMBER/NAME does not affect output.
NULL SUPPRESS/NULL controls the display of null values for a PRINT command. If NULL SUPPRESS is in effect, null items do not display. If NULL is in effect, the word -NULL- is displayed for items with no values.
NULL SUPPRESS/NULL and REPEAT/REPEAT SUPPRESS have no affect on records written to a Collect File.
ZERO SUPPRESS/ZERO specifies whether nulls are to be treated as zeros in arithmetic expressions. ZERO SUPPRESS causes null values in INTEGER, DECIMAL, and MONEY item types to be treated as zeros in ad hoc or stored functions. ZERO causes nulls to be changed to zero for items included in computations. ZERO/ZERO SUPPRESS affects a system function if the object of the system function is enclosed in parentheses.
TREE/RECORD controls the output of lower level data records when you specify a record in the retrieval-clause of a PRINT or UNLOAD command. TREE displays data trees whose top records are occurrences of the specified record. RECORD displays only data records that are occurrences of the specified record.
If RECORD is in effect for a RELOAD, only level 0 records are reloaded. If RECORD is in effect for a MAP that causes restructuring, only level 0 records are retained. Descendant records are lost.
REPEAT/REPEAT SUPPRESS controls the repeated display of data from common ancestors of selected data records. When REPEAT is in effect and two or more selected data records have common ancestors, data from those ancestor records are displayed each time data from a selected record are displayed. With REPEAT SUPPRESS, if successive selected data records have a common ancestor, values from that ancestor record are displayed only once, and the values from each data tree appear in logical order.
HEX OFF/HEX ON determines whether UNDEFINED output values appear in HEX notation or display format.
Although you cannot specify format options in a COPY TREE command, the command honors the TREE/RECORD format option in effect. That is, if RECORD is in effect, COPY TREE duplicates only the top node of the selected data tree.

FRAME and END FRAME

Establishes a logical unit of work for Coordinated Recovery, or places a global hold on the database, or both. If the Rollback Log is enabled, each FRAME and END FRAME command causes a database synchpoint.

Syntax

FRAME [/IMMEDIATE/] :

END FRAME :

Examples

FRAME /IM/:

END FR:

Rules

Abbreviations:
```
     IMMEDIATE = IM
     FRAME = FR
```
Use the IMMEDIATE syntax to maintain a previously obtained local hold.
You cannot issue the commands DEFINE, CONTROL, REPORT, COMPOSE, or EXIT between FRAME and END FRAME commands.

INSERT TREE

Inserts new data trees where trees do not exist.

Syntax

INSERT TREE record EQ |value-stream END* |before-clause :
                     |PREVIOUS        |after-clause

INSERT TREE record full-trace EQ |value-stream END* :
                             |PREVIOUS

INSERT TREE record partial-trace EQ |value-stream END* where-clause:
                               |PREVIOUS

record: is a schema record name or component number.
value-stream: is a series of component numbers and associated data values in the same format as the loader stream. The record number to the left of the EQ operator is not included in the value stream. See LOAD for the format of a loader stream.
PREVIOUS: repeats the value stream in the preceding update command.
before-clause: is the same as a where-clause, except that the keyword BEFORE replaces the keyword WHERE. See Where-Clause.
after-clause: is the same as a where-clause, except that the keyword AFTER replaces the keyword WHERE. See Where-Clause.
full-trace: is a list of one or more positive integers or 0, each preceded by the system separator (for example, *1*4*0). In a left-to-right scan, each integer designates the position of the child record under its parent at each level. The rightmost integer designates the level 0 position. Zero means last.
partial-trace: is a trace in which the rightmost integer designates a position other than level 0.
where-clause: selects specific data records. See Where-Clause.

Examples

IT C420 EQ 421*422*COBOL*423*09/09/1986*END* AFTER C2 = REID
   AND C421 = TECNIK:

INSERT TREE C120 *1*1*1 EQ 121*06/30/91* END* WH C1 EXISTS:

IT C120 *1*1*1 EQ 121*05/29/91*122*184.00*123*10.00*124*1950.00*
   125*153.20*126*1560.35*END* WHERE C1 EQ 1120:

IT C200 *2 EQ 201*COBOL*202*GOOD*203*1*END* WH C1 = 1120:

IT C0 *0 EQ 100*101*PROGRAMMER*102*INFORMATION SYSTEMS*130*131*
   1.0*132*NEW EMPLOYEE*END*:

Rules

Abbreviations:

     AFTER = AF
     BEFORE = BE
     INSERT TREE = IT

If the value stream is not included, an empty record is added.
If INSERT TREE contains a before-clause or an after-clause, the record at the top of the new subtree is placed immediately before or after each selected record.
If the value stream contains a record number immediately followed by the system separator and another record number, an empty record is inserted for the former record.
You must have U-authority for components specified in an INSERT TREE command.
The command referenced by PREVIOUS cannot contain errors.
When specifying PREVIOUS, the record preceding EQ must be the same record as in the previous update command.
You cannot use Collect File items in an INSERT TREE action-clause.
You cannot issue INSERT TREE for a damaged database.

LIMIT and END LIMIT

Does three things: sets a specific number of records on which retrieval or update commands are to be executed, sets a minimum and maximum for the number of records on which retrieval or update commands are to be executed, or controls the output of selected records in retrieval commands.

Syntax

LIMIT k :

LIMIT k , m |[/CANCEL] :
            |[/TRUNCATE]

LIMIT k , m |[/CANCEL] , j [/fileref] :
            |[/TRUNCATE]

END LIMIT :

k, m, j: are positive integers or 0. Zero means infinite for m and j. Also, k and j must be less than or equal to m except when m equals 0. The j intermediate limit has no effect on Collect File commands.
CANCEL
TRUNCATE: controls the output of selected records. The default is CANCEL; that is, if the number of selected records falls outside the range specified, the command is cancelled.
fileref: is a DDname declared in the JCL. The default is the Report File.
END LIMIT: resets the software to the default, which is unlimited.

Examples

LIMIT 3:

LIMIT 3, 5:

LIMIT 3, 3/TRUNCATE:

LIMIT 3, 7, 5:

LIMIT 3, 7, 5/ALTFILE:

LIMIT 3, 7/TRUNCATE, 5:

LIMIT 3, 7/TRUNCATE, 5/ALTFILE:

END LIMIT:

Rules

At the beginning of each session, all limits are set to 0 (that is, unlimited).
If you set j, a Collect File where-clause is not allowed for a retrieval command.
If you set any limits, an update command with a Collect File where-clause is not allowed.

LIST

Retrieves database and Collect File information and displays it in columnar format.

Syntax

LIST [/[options] {,TITLE specifications]/] retrieval-clause
     [[,ordering-clause] where-clause] :

options

is a list of one or more of the following format options, separated by commas. Defaults are listed in the first column.

INDENT               BLOCK
NULL SUPPRESS        NULL
NUMBER               NAME
REPEAT               REPEAT SUPPRESS
SINGLE SPACE         DOUBLE SPACE
STUB                 STUB SUPPRESS
TREE                 RECORD
ZERO SUPPRESS        ZERO
HEX OFF              HEX ON

See FORMOP for details on format options.

TITLE

allows you to include layout specifications. Use a comma in front of the keyword TITLE only if you have specified a format option. Title and paging specifications can occur anywhere after the keyword TITLE.

specifications

See Layout Specifications.

retrieval-clause

specifies a list of one or more retrieval objects, separated by commas. See Retrieval-Clause.

ordering-clause

specifies the order of selected data records. A where-clause is required if an ordering-clause is specified. See Ordering-Clause.

where-clause

selects specific records. See Where-Clause.

Examples without Layout Specifications

LIST EMPLOYEE NUMBER, LAST NAME WHERE EMPLOYEE NUMBER LE 1005:

LIST C2, C1, C6, ORDERED BY C2 WHERE C1 <= 1003:

LIST LAST NAME, *C2001*, (C106 - C105), AVG C111 WHERE C106 EXISTS:

LIST/DOUBLE SPACE, REPEAT SUP/ C2,C4,C7,C15,C16 WHERE C1 <= 1003:

Layout Specifications

specifications [(page-width)] [title] [paging] [rows]

(page-width)

is the maximum number of characters for a line, 4 to 131. The defaults are 131 for batch and 71 for interactive.

title

D (starting-column) [title-line-text]

paging

F (lines-per-page) [footing-line-text]

Lines-per-page must be from 10 to 60.

rows |L (width) [column-heading] [,S-specification]
     |R
     |B

L

specifies left-justified column heading.

R

specifies right-justified column heading.

B

specifies a blank column; optional heading is left-justified.

Not specifying L, R, or B directs the software to left-justify the heading text and to allow the longest of the three lines possible to determine the width of the columns.

(width)

is the column heading width.

column-heading

[[text] + ] [[text] + ] text

S-specification

tells the software where to start a new row, how to space between rows of column headings, and how to space between corresponding rows of values. For syntax, see LIST command in SYSTEM 2000 QUEST Language and System-Wide Commands, Version 12, First Edition

Examples with Layout Specifications

LIST /TITLE R(16)MAJOR FIELD, L(16)MINOR FIELD/ C414, C415 WHERE
   C415 EXISTS AND C1 LE 1100:

LIST /TITLE (45) D(15) REPORT TITLE, F(15)     --FOOTING LINE--,
   L(10) LAST NAME, EMPLOYEE+NUMBER, R(15) SOCIAL     +SECURITY
   NUMBER/ C2, C1, C6 WHERE C1 =< 1005:

LIST /DOUBLE SPACE, TITLE (40)  D(7)REPORT TITLE,
   F(40)     --FOOTING LINE--,
   L(10) LAST NAME,S(1,2),B(10),
   L(8)EMPLOYEE+ NUMBER,R(15)SOCIAL     +SECURITY NUMBER,S(2)/
   C2, C1, C6 WHERE C1 LE 1005:

LI/TITLE D(20)*NOW*/C1, C2, C3 WH C1 <= 1010:

Rules

Do not use a record name or number in the retrieval-clause, except with the BY operator or with a COUNT system function.
Use commas to separate title, paging, and row specifications.
Page width must accommodate title and footing line text; the page number on the title line uses five spaces.
Do not include the command terminator, a comma, or a slash in the title line text and footing line text.
Column heading text cannot include a comma, plus sign, command terminator, or slash.
You can specify up to three lines of column heading text. With the S-specifications, you can define up to five rows of column headings.
Total width of columns must be at least two spaces less than page width in order to contain row identifiers and space at the left of each row.
You can include any number of B-specifications but no more than 59 specifications for data columns. B-specifications can be adjacent.
You cannot use the system string *TODAY* in a layout specification because slashes in this case are treated as delimiters. You can, however, use the system strings *FTODAY* and *NOW* in a layout specification.

LOAD

Loads data into a database in logical order.

Syntax

LOAD :

Example

LOAD:

Rules

When you issue this command, the software looks to the Data File for a loader stream. Therefore, you must first issue the DATA FILE IS command.
A loader stream consists of a group of values, with each value preceded by an item number and followed by a system separator. Each group of values (a data record) is preceded by a schema record number. The item numbers and record numbers are not preceded by a C.
You indicate the end of a data record by either another schema record number or an entry terminator (END*).

You indicate the end of the loader stream by following the last entry terminator with a system separator. For example,

1*1120*2*REID*3*DAVID G/*4*01/16/1988*5*08/15/1957*6*441-04-0121* */
   .
   .
   .
420*421*TECNIK*422*SYSTEM RS ADVANCED*423*03/18/1989*END**

Enable values padding before doing a LOAD operation. After the load processing completes, you should disable values padding or too much table space will be added during ad hoc updates. See ENABLE/DISABLE VALUES PADDING.
To check for errors before loading, issue the DISABLE EXECUTION command or the STOP IF command before issuing the LOAD command.
When the loading process is complete, the database cycle number is incremented by one.

MOVE TREE

Moves a data record and its descendants from one position in a database to another without affecting the database tables or index.

Syntax

MOVE TREE record |where-clause               TO |before-clause              :
                |partial-trace where-clause        |after-clause
                |full-trace                     |partial-trace where-clause
                                             |full-trace

record: is a schema record name or component number; the target record.
where-clause: selects specific records. See Where-Clause.
partial-trace: is a trace in which the rightmost integer designates a position other than level 0.
full-trace: is a list of one or more positive integers or 0, each preceded by the system separator (for example, *1*4*0). In a left-to-right scan, each integer designates the position of the child record under its parent at each level. The rightmost integer designates the level 0 position. Zero means last.
before-clause: is the same as a where-clause except that the keyword BEFORE replaces the keyword WHERE. See Where-Clause.
after-clause: is the same as a where-clause except that the keyword AFTER replaces the keyword WHERE. See Where-Clause.

Example

MT C110 WH C113 EQ 03/01/90 AND C1 EQ 1120 TO AFTER C113 = 03/01/91
   AND C1 = 1120:

Rules

Abbreviations:

     AFTER = AF
     BEFORE = BE
     MOVE TREE = MT

You must have U-authority for components specified in a MOVE TREE command.
If Security by Entry is in effect, a secondary password holder can move a subtree from one logical entry to another, provided the secondary password holder knows both entry key values.
You cannot issue MOVE TREE for a damaged database.

Ordering-Clause

Specifies the output order of data to be retrieved with PRINT, LIST, UNLOAD, and COLLECT commands. Can also be used with RELOAD and MAP.

Syntax

,ORDERED BY sort-keys

sort-keys

is a list of one or more items, records, ad hoc functions, stored functions, or system functions in the following format, separated by commas.

|[LOW] unit
|[HIGH]

LOW

specifies low-to-high (ascending) order, for example, A,B,C,D or 1,2,3,4. The default is LOW.

HIGH

specifies high-to-low (descending) order, for example, Z,Y,X,W or 9,8,7,6.

unit

|item
|record
|stored-function
|(adhoc-function)
|system-function

item

is a schema item name or component number.

record

is a schema record name or component number.

stored-function

is a stored function name or component number with optional parameters.

adhoc-function

is a mathematical expression enclosed in parentheses. See Ad Hoc Function.

system-function

allows you to obtain simple arithmetic statistics about values stored in a database. System functions include: AVG, COUNT, MAX, MIN, SIGMA, and SUM. A by-phrase is required if you use a system function in an ordering-clause. See System Function.

Examples

PRINT C1, C2, C10, ORDERED BY C1, C10 WHERE C1 EXISTS:

UNLOAD C1, OB C1 WH C1 EXISTS:

PR C1, C2, C11, OB HIGH C1, LOW C2, HIGH C11 WH C11 EXISTS:

Rules

Abbreviation: ORDERED BY = OB
A comma must precede the keywords ORDERED BY.
If you specify an ordering-clause, a where-clause is required.
The order in which you enter sort keys determines the order that the retrieved data are displayed.
An item can be key or non-key.
An ordering-clause that specifies a system function requires a by-phrase. See By-Clause .
Components in an ordering-clause must be in the same path as the target record in the retrieval-clause and as the focal record in the where-clause.
Digits in numeric values for nonnumeric items are treated as characters (not numbers) for sorting purposes.
Collect File items cannot be specified in an ordering-clause.

PRINT

Retrieves database or Collect File information and displays it in a simple, sequential list.

Syntax

PRINT [/[options] [,TITLE comment]/] retrieval-clause
     [[,ordering-clause] where-clause] :

options

is a list of one or more of the following format options, separated by commas. Defaults are listed in the first column.

INDENT               BLOCK
NULL SUPPRESS        NULL
NUMBER               NAME
REPEAT               REPEAT SUPPRESS
SINGLE SPACE         DOUBLE SPACE
STUB                 STUB SUPPRESS
TREE                 RECORD
ZERO SUPPRESS        ZERO
HEX OFF              HEX ON

See FORMOP for details on format options.

TITLE

allows you to insert a comment within the output. (If you do not specify format options, do not include a comma before the keyword TITLE.)

comment

retrieval-clause

specifies a list of one or more retrieval objects, separated by commas. See Retrieval-Clause.

ordering-clause

specifies the order of selected data records. A where-clause is required if an ordering-clause is specified. See Ordering-Clause.

where-clause

selects specific data records. See Where-Clause.

Examples

PRINT C1, C2:

PR C100 WHERE C1 = 1265:

PRINT/DOUBLE SPACE,INDENT/ C2,C4,C7,C15,C16 WHERE C1 LE 1003:

PRINT /TITLE THIS TEXT DISPLAYED IN MESSAGE FILE/ C1, C2, C3:

Rules

Abbreviation: PRINT = PR

QUEUE

Calls the QUEUE processor.

Syntax

QUEUE :

Example

USER, DEMO:
DBN IS EMPLOYEE:
   .
   .  (QUEST commands)
   .
QUEUE:                 (calls QUEUE)
   .
   .
   .

Rules

For information about QUEUE processor commands, see Technical Report: S2-110 QUEUE Language for SYSTEM 2000 Software, Release 11.6 under IBM OS and CMS

RELOAD

Reconstructs database files.

Syntax

RELOAD [[ordering-clause] where-clause] :

ordering-clause: specifies the order of selected data records. You can specify only level 0 items. A where-clause is required if an ordering-clause is specified. See Ordering-Clause.
where-clause: selects specific data records. You cannot include the SAME operator. See Where-Clause.

Examples

RELOAD:

RELOAD WHERE EMPLOYEE NUMBER < 5075:

RELOAD ORDERED BY C1 WHERE C1 EXISTS:

Rules

Only the master password holder or the DBA password holder (with authorization) can issue this command. However, the DBA password holder cannot issue a RELOAD command containing a where-clause.
You can issue RELOAD from the CONTROL or QUEST processor, but in either case, the CONTROL processor is attached after execution.
This command requires the same amount of scratch file space as loading the database does.
If you include a where-clause and the LIMIT command has been issued, only the number of selected data records within the specified limits are loaded.
This command resets the cycle number to zero at the beginning of the reload process and increments it by one when the reloading is finished. However, if a specified where-clause produces no qualified data records, the cycle remains reset to zero.
Issuing this command suspends update logging.
If the RECORD format option is in effect, only level 0 data records are reloaded.
In a Multi-User environment, you must have exclusive use of the database to issue this command.

REMOVE

Replaces values with nulls in selected data records.

Syntax

REMOVE item full-trace :

REMOVE item [partial-trace] where-clause :

REMOVE record full-trace :

REMOVE record [partial-trace] where-clause :

item: is a schema item name or component number.
full-trace: is a list of one or more positive integers or 0, each preceded by the system separator (for example, *1*4*0). In a left-to-right scan, each integer designates the position of the child record under its parent at each level. The rightmost integer designates the level 0 position. Zero means last.
partial-trace: is a trace in which the rightmost integer designates a position other than level 0.
where-clause: selects specific data records. See Where-Clause.
record: is a schema record name or component number.

Examples

RE CURRENT DEDUCTION WH C113 EQ 03/01/90 AND C1 EQ 1120:

REMOVE C302 WHERE C1 = 1043:

REMOVE ENTRY WHERE C1 FAILS:

Rules

Abbreviation: REMOVE = RE
REMOVE affects only those records that are occurrences of a single schema record.
REMOVE eliminates either the value of a single item or the values of all items in selected records.
You must have U-authority for components specified in the action-clause of a REMOVE command.
You cannot use Collect File items in a REMOVE action-clause.
If you remove the values for all items in a record, the record remains in the database (as a null record). Only REMOVE TREE removes records.
You cannot issue REMOVE for a damaged database.

REMOVE TREE

Removes data trees (including the data) from a database.

Syntax

REMOVE TREE record full-trace :

REMOVE TREE record [partial-trace] where-clause :

record: is a schema record name or component number; the target record.
full-trace: is a list of one or more positive integers or 0, each preceded by the system separator (for example, *1*4*0). In a left-to-right scan, each integer designates the position of the child record under its parent at each level. The rightmost integer designates the level 0 position. Zero means last.
partial-trace: is a trace in which the rightmost integer designates a position other than level 0.
where-clause: selects specific data records. See Where-Clause.

Examples

REMOVE TREE ENTRY WHERE C2 = REID:

RT C130 *2 WHERE C1 EQ 1340:

Rules

Abbreviation: REMOVE TREE = RT
REMOVE TREE removes the specified record and all descendant records.
You must have U-authority for components specified in a REMOVE TREE command.
You cannot use Collect File items in a REMOVE TREE action-clause.
You cannot issue REMOVE TREE for a damaged database.

REPORT

Calls the REPORT processor.

Syntax

REPORT :

Example

USER, DEMO:
DBN IS EMPLOYEE:
   .
   .  (QUEST commands)
   .
REPORT:                (calls REPORT)
   .
   .
   .

Rules

For information about REPORT processor commands, see SYSTEM 2000 REPORT Language, Version 12, First Edition.

Retrieval-Clause

Specifies a list of components to be retrieved with a LIST, PRINT, UNLOAD, or COLLECT command. Can include records, items, stored strings, stored functions, ad hoc functions, system functions, by-clauses, and Collect File items -- separated by commas. Can be followed by an ordering-clause, a where-clause, or both.

Syntax

units

unit

|item
|record
|* function *
|* function (parameters)
|* string *
|* string (parameters)
|(adhoc-function)
|system-function
|by-clause
|cf-item

item

is a schema item name or component number.

record

is a schema record name or component number. The record cannot be specified in the retrieval-clause of a LIST command unless it appears in a by-phrase or with the COUNT system function.

function

is a stored function name or component number.

string

is a stored string name or component number.

parameters

are the values that replace the numbered parameters in the stored function or string.

adhoc-function

is a mathematical expression enclosed in parentheses. See Ad Hoc Function.

system-function

allows you to obtain simple arithmetic statistics about values stored in a database. System functions include: AVG, COUNT, MAX, MIN, SIGMA, and SUM. See System Function.

by-clause

specifies that only values for certain items are to be displayed in logical order. See By-Clause.

cf-item

is a Collect File item name. The item name must be followed by (CF) if it has the same name as a schema component.

Examples

PR C1, C2, C3:

PRINT LAST NAME WHERE ACCRUED VACATION EXISTS:

LIST C1, C2, C3, *C2010* WH C1 LE 1010:

PR (C114 + C126) WH C1 EQ 1010:

PR SUM C124 BY C110, SUM C124 BY DATA BASE WH C1 = 1120:

PR MIN C2, MAX C2:

PR C1, (*FTODAY* - C4) WH C1 <= 1004:

Rules

You can specify up to 59 units (five rows) in a LIST command retrieval-clause.
If a retrieval command includes a where-clause and if no by-phrases are specified in the retrieval-clause, all retrieval objects in the retrieval-clause must be related to the qualified records.
If a retrieval command includes a where-clause and if one or more by-phrases are specified in the retrieval-clause, the retrieval objects preceding the first by-phrase, as well as all records specified in the by-phrases, must be in the path of the target record. Also, the records in the by-phrases must be related to the records qualified by the where-clause.
You must have R-authority for components specified in a retrieval-clause.
The retrieval-clause can contain a mixture of schema components and Collect File items if there is a where-clause containing a condition with a Collect File item.

SAME IS

Tells the software which retained list of qualified data records to use when the SAME operator is specified.

Syntax

SAME IS DYNAMIC :

SAME IS STATIC :

Example

SA IS STATIC:

Rules

Abbreviation: SAME = SA
At the beginning of each session, SAME is set to DYNAMIC.
The DYNAMIC setting allows you to issue a sequence of commands acting on an evolving collection of data records.
The STATIC setting allows you to issue a sequence of commands acting on a static collection of data records. For each individual command, the collection can be modified with additional qualification criteria.

STOP IF/CONTINUE IF

STOP IF specifies a limit of command errors that can occur before the software ends a session. CONTINUE IF reverses the limit set in a STOP IF command.

Syntax

STOP [AFTER SCAN] IF |[ANY] [UPDATE] |COMMAND IS    REJECTED :
                     |[n]            |COMMANDS ARE

CONTINUE [AFTER SCAN] IF |COMMAND IS    REJECTED :
                         |COMMANDS ARE

ANY: is the default maximum error count, which specifies 1.
n: is the maximum error count. The number must be greater than 0.
UPDATE: If you omit the UPDATE option, all command errors are counted.

Examples

STOP IF 2 COMMANDS ARE REJECTED:

CONTINUE IF COMMANDS ARE REJECTED:

STOP IF ANY COMMAND IS REJECTED:

STOP IF 2 UPDATE COMMANDS ARE REJECTED:

STOP AFTER SCAN IF 9 COMMANDS ARE REJECTED:

Rules

Abbreviation: AFTER = AF
The status of STOP IF remains the same if you change processors.

System Function

Obtains simple statistics about database values.

Syntax

|AVG   unit [by-phrase]
|COUNT
|MAX
|MIN
|SIGMA
|SUM

unit

|item
|record
|stored-function
|(adhoc-function)
|cf-item
|(system-function)

item

is a schema item name or component number.

record

is a schema record name or component number.

stored-function

is a stored function name or component number with optional parameters.

adhoc-function

is a mathematical expression enclosed in parentheses. See Ad Hoc Function.

cf-item

is a Collect File item name.

by-phrase

BY |record
   |DATA BASE

See By-Clause .

system-function

You can have one system function inside another. The inner system function must be enclosed in parentheses.

Examples

PRINT MIN C2, MAX C2:

PR COUNT C126, MIN C126, MAX C126, SUM C126, AVG C126, SIGMA C126:

PR SUM C124 BY C0 WH C1 LE 1010:

PR AVG (SUM C124 BY C0):

PR (((MIN C126) + (MAX C126))/2):

Rules

You can use system functions in the action-clause of a LIST, PRINT, or UNLOAD command, in an ordering-clause with a by-phrase, and in update commands if the system functions are enclosed in parentheses.
AVG produces the average of the values stored for the requested numeric item.
COUNT produces a count of the number of times a data value exists or a data record occurs. This is the only system function you can use with records.
MAX produces the maximum value stored for the requested item.
MIN produces the minimum value stored for the requested item.
SIGMA produces the standard deviation for the requested numeric item.
SUM adds all the values for the requested numeric item.
You can use any system function with a by-phrase, but the by-phrase should contain an ancestor record name or number.
You can use SUM, AVG, and SIGMA with numeric item types only (including dates).
You cannot use Collect File items with system functions.
If you use a system function in an ad hoc function, the system function must be enclosed in parentheses.
When the ZERO format option is in effect and when the object of a system function is enclosed in parentheses, nulls are treated as zero.

System String

Provides the current date in date and function format and the current time according to your computer's internal calendar and clock.

Syntax

*NOW*            current time

*TODAY*          current date

*FTODAY*         current date in function format

Examples

PRINT C1, (*FTODAY* - C4) WHERE C1 LE 1004:

LIST/TITLE D(20) *NOW*/ C1, C2, C3 WH C1 <= 1010:

Rules

Use *FTODAY* in ad hoc and stored functions.
Use *TODAY* and *NOW* in update and retrieval commands.
Use *NOW* to time-stamp reports.

The default formats for time and date are:

     *NOW*      HH.MM.SS

     *TODAY*    MM/DD/YYYY

     *FTODAY*   MM.DD.YYYY

You cannot use *TODAY* in the layout specifications of a LIST command because the slashes in this case are treated as delimiters.
You can use the system strings in a Command File for updates, but they cannot be used in a Data File.

TALLY

Provides summary information about the values stored for key items in a database.

Syntax

TALLY |[/EACH/] items :
      |[/ALL/]

items: is a list of one or more key item names or component numbers, separated by commas. The maximum is 96 items.

Examples

TA /EACH/ C8:

TALLY C7, C8, C9, C102:

Rules

Abbreviation: TALLY = TA
At the beginning of each session, TALLY is set to EACH.
EACH lists each distinct value for the specified items as well as the number of times each value occurs, along with the total number of values and total occurrences.
ALL lists only the minimum and maximum values for the specified items, along with the total number of distinct values and total occurrences.
You must have R-authority for items specified in a TALLY command.
If Security by Entry is in effect, only the master password holder can issue TALLY.
Issuing the LIMIT command affects TALLY output. Only values whose number of occurrences fall in the range of the limits are displayed.
SINGLE SPACE/DOUBLE SPACE is the only format option that affects TALLY.

UNLOAD

Retrieves data in a format suitable for use in a Data File.

Syntax

UNLOAD :

UNLOAD [/[options] [,TITLE comment]/] retrieval-clause
     [[,ordering-clause] where-clause] :

UNLOAD/COLLECT FILE [,options] [,TITLE comment]/ :

options

is a list of one or more of the following format options, separated by commas. Defaults are listed in the first column.

INDENT               BLOCK
NULL SUPPRESS        NULL
NUMBER               NAME
REPEAT               REPEAT SUPPRESS
SINGLE SPACE         DOUBLE SPACE
STUB                 STUB SUPPRESS
TREE                 RECORD
ZERO SUPPRESS        ZERO
HEX OFF              HEX ON

See FORMOP for details on format options.

TITLE

allows you to insert a comment within the output. (If you do not specify format options, do not include a comma before the keyword TITLE.)

comment

retrieval-clause

specifies a list of retrieval objects, separated by commas. See Retrieval-Clause.

ordering-clause

specifies the order of selected data records. A where-clause is required if an ordering-clause is specified. See Ordering-Clause.

where-clause

selects specific data records. See Where-Clause.

Examples

UNLOAD:

UN C1, C2:

UNLOAD C100 WH C1 = 1265:

UNLOAD C1, C2, JOB SKILLS WH C1 EQ 1120:

UNLOAD /RECORD/ C100 WH C1 = 1265:

UN C1, C2, SUM C126, (C124 - C114) WH C1 EQ 1265:

UNLOAD /CF/:

Rules

Abbreviations:

     UNLOAD = UN
     COLLECT FILE = CF

Issuing UNLOAD without any syntax options produces only the system separator, which appears in column 1 in the Report File. You can utilize this marker for your own purposes, such as to separate output from different commands.
Specifying the DOUBLE SPACE format option causes errors because of unwanted blanks between lines.
Use a comma in front of format options only if you specify COLLECT FILE.
A Collect File can be unloaded only in its entirety.
The software checks for the current system separator in any CHARACTER, TEXT, or UNDEFINED value being unloaded. If one occurs, message -290- appears. You can disable this checking with the execution parameter OPT000. You can also request a check for all characters that can be specified as the system separator.

Where-Clause

Selects a subset of a database for retrieval or update commands. A where-clause consists of one or more expressions following the keyword WHERE. Each expression is made up of one or more conditions that an item value must meet.

Syntax

WHERE expression

expression

|condition
|(expression)
|NOT expression
|expression AND expression
|expression OR expression
|record HAS expression
|expression AT n
|SAME

condition

[NON-KEY] item |unary-operator
               |binary-operator value
               |ternary-operator value * value
               |CONTAINS text
               |* binary-operator item *
               |* binary-operator cf-item [(CF)] *
               |IN (value, value [, value])
               |CTNSIN (value, value [, value])

NOT

finds the complement of specified criteria.

AND

combines two expressions by finding data records that satisfy both expressions.

OR

combines two expressions by finding data records that satisfy either expression or both.

record

is a schema record name or component number.

HAS

specifies a focal record to qualify data records based on the contents of descendant or ancestor data records.

AT

specifies a data record by its position under its parent.

n

is a 0 or a positive integer indicating position. Zero means last position.

SAME

repeats the where-clause of a previous command with or without additional qualification criteria.

item

is a schema item name or component number. The item can be key or non-key.

unary-operator

EXISTS or FAILS verifies existence or nonexistence of values for an item.

binary-operator

EQ, NE, GE, GT, LE, or LT

ternary-operator

EQ, NE, or SPANS compares data values with a range of values.

value

is a literal value or a system string (*NOW*, *TODAY*, *FTODAY*). Enclose the value with a delimiter of your choice. The default is the slash. Ternary operators require a low value and a high value.

CONTAINS

searches for characters within an item's value.

text

uses the following syntax:

[[THE] CHARACTERS] substring
   [* substring [correlation]] |[position]
                          |[interval]

[THE] |WORD   substring  |[position]
      |PREFIX          |[interval]
      |SUFFIX

For details on searching for characters using CONTAINS, see SYSTEM 2000 QUEST Language and System-Wide Commands, Version 12, First Edition

cf-item

is a Collect File item name. The item name may be followed by (CF) to resolve a conflict between a schema item name and a Collect File item name.

IN

causes the software to create a condition for each specified value. The conditions are connected with the OR operator. Each condition contains the specified item, the EQ operator, and one of the specified values.

CTNSIN

causes the software to create a condition for each specified value. The conditions are connected with the OR operator. Each condition contains the specified item, the CONTAINS operator, and one of the specified values. The software searches for the characters within the item's value.

Examples

PRINT LAST NAME WHERE ACCRUED VACATION EXISTS:

PR LAST NAME WH EMPLOYEE STATUS NE FULL TIME OR EMPLOYEE
   STATUS FAILS:

LI C2, C3 WH C13 FAILS AND C4 GE 01/01/91:

PR C2 WH HIRE DATE SPANS 01/01/88*12/31/1990:

PR C2 WH (MAJOR FIELD = MATH OR MINOR FIELD = MATH)
   AND GENDER EQ FEMALE:

PR C2 WH C0 HAS C201 EQ FORTRAN AND C0 HAS C201 EQ COBOL:

PR C101 WH START DATE* = HIRE DATE*:

LIST C102, C103 WHERE C102 EXISTS AT 1:

AS C14 EQ 2311 HANSFORD* WH C411 EQ /COLLEGE OF WILLIAM AND MARY/:

LI BY ENTRY, C1, C2, C3, C101, C102, C7, C111, OB C2 WH C413
   GT 06/01/1986 AND (C412 IN (MA, MBA, MS, PHD)).

LI C1, C2, C3 WH LAST NAME CONTAINS MAC:

LI C2, C3, C10, OB C2 WH C10 CONTAINS THE CHARACTERS 100*199 WITH
   MATCH ON 3 CHARACTERS FROM POSITION 7 THRU LAST :

PR C1, C2 WH LAST NAME IN (SMITH, JONES):

PR C1, C2 WH LAST NAME CTNSIN (MC, SON, TON):

Rules

Abbreviations:

     CHARACTERS = CHARACTER or CHAR
     CONTAINS = CONT, CONTAIN, or CONTAINING
     EXISTS = EXIST or EXISTING
     FAILS = FAIL or FAILING
     HAS = HAVE or HAVING
     NON-KEY = NK
     SAME = SA
     SPANS = SPAN or SPANNING
     WHERE = WH

You must have W-authority for components specified in a where-clause.
In some forms of the INSERT TREE and MOVE TREE commands, you need the keywords BEFORE or AFTER instead of WHERE.
You can also use symbols for the following operators:
```
     Operator       Symbol

     EQ              =
     GE              >= or => or ^< or !<
     GT              >
     LE              <= or =< or ^> or !>
     LT              <
     NE              ^= or !=
     AND             &
     OR              |
     NOT             ^ or !
```
Without symbols, the software reads a blank on either side of an operator to separate it from other syntactic units. However, with symbols, blanks are not required for separation. Any blanks encountered following a symbol are considered part of the value. For example, these two where-clauses are equivalent:
```
WHERE C1 EQ ABC AND C2 GT DEF:

WHERE C1=ABC&C2>DEF:
```
If component names contain any of the symbols, use the component numbers. Also, if you change the system separator to be any of the symbols, syntax errors could occur if you were to specify that symbol in the where-clause.
Parentheses control the order of processing, and they take precedence over operators.
Operator precedence is as follows: AT, HAS, AND, OR.
When all conditions are joined by the same operator, key-conditions are processed first from right to left. Non-key conditions are processed later.
When a value includes any syntactic unit (such as the command terminator, the system separator, OR, AT, AND, NOT, or a right parenthesis), you must enclose the value with delimiters. The current delimiter must not occur in a value enclosed by a delimiter.
You can use only items with the unary operators EXISTS and FAILS.
When comparing two items with a binary operator, both items must belong to related schema records, and both must be either numeric or nonnumeric. The system separator must follow each specified item.
Use a Collect File item only as the right-hand term of an item-to-item comparison.
You cannot specify a range of values for the IN and CTNSIN operators. All characters between the commas are treated as a single value.
For ternary operators, the low value must be less than or equal to the high value as determined by the collating sequence. See Collating Sequence and Character Set in Appendices .
If the conditions in a where-clause combined by AND or OR contain components that are disjoint, you must use the HAS operator.
You can specify more than one AT operator if the operators are separated by parentheses.
If two AT operators are given, one must be AT 0.
You must specify the keyword NON-KEY to change a key condition into a non-key condition.
To specify the SAME operator in a command, you must first issue a QUEST command with a where-clause. You cannot use SAME if it is joined with an unindexed expression by the binary operator OR. See SAME IS .
Under Security by Entry, the first or only condition in the where-clause must be a key-condition with the EQ operator. If the condition specifying the Entry Key item is not the only condition in the where-clause, it must be joined to the other condition or expression with the AND operator.
The software guarantees that floating point values are precisely compared. "Fuzzy" logic does not creep into where-clause logic. If you do not know the precise value stored in the database, use the SPANS operator to locate a range of imprecise values.

Resources

SYSTEM 2000 Quick Reference Guide QUEST Language Commands

ADD

Syntax

Examples

Rules

Ad Hoc Function

Syntax

Examples

Rules

ASSIGN

Syntax

Examples

Rules

ASSIGN TREE

Syntax

Example

Rules

By-Clause

Syntax

Examples

Rules

CHANGE value

Syntax

Examples

Rules

CLEAR

Syntax

Examples

Rules

COLLECT

Syntax

Examples

Rules

COPY TREE

Syntax

Examples

Rules

DATE FORMAT IS

Syntax

Example

Rules

DESCRIBE

Syntax

Examples

Rules

DITTO

Syntax

Example

Rules

ENABLE/DISABLE EXECUTION

Syntax

Examples

Rules

ENABLE/DISABLE VALUES PADDING

Syntax

Example

Rules

FORMOP

Syntax

Example

Rules

FRAME and END FRAME

Syntax

Examples

Rules

INSERT TREE

Syntax

Examples

Rules

LIMIT and END LIMIT

Syntax

Examples

Rules

LIST

Syntax

Examples without Layout Specifications

Layout Specifications

Examples with Layout Specifications

Rules