Many of the ACCENT R Interactive Command Language (ICL) commands can be augmented by the clauses described in this chapter. These clauses are used to:
Control record access order.
Identify subsets of records.
Define record matching between master and transaction data sets.
Display information on the screen.
Identify output data sets.
Identify PM’s that further control action.
Identify the transaction data set.
The clauses are described here in alphabetical order for easy reference.
PURPOSE: In the commands MERGE, PURGE, SELECT and UPDATE, the ADVANCE clause controls how the master Data Set and transaction Data Set (DS) process against one another after a match has been found.
{/ADVANCE TRANSACTION/;ADVANCE MASTER;ADVANCE
{MASTER AND TRANSACTION;BOTH}} /WHEN MATCHED/
|
is the default condition and applies when no ADVANCE clause is specified. Under this option, when a transaction record matches a master record, ACCENT R applies successive transaction records against the same current master record until an unmatched record is found. |
|
|
causes the first matching transaction record to be processed against all matching master records for non-indexed processing. When a Data Index is declared, ADVANCE MASTER applies every matching transaction to every matching master. See Figure 7-1. |
|
|
ADVANCE MASTER and TRANSACTION or BOTH |
causes both master and transaction sets to advance when a match has been found and processed. See Figure 7-2. |
|
are clarity words that can be included or not at the user's discretion. |
The following examples show the effect of the three ADVANCE possibilities. All three use the Data Set BOOKS_DBM2 as the master set and NEWBOOK as the transaction DS. @DATE is July 20, 1990. The first SELECT command represents default matching (ADVANCE TRANSACTION).
*USE DS NEWBOOK
*EXTRACT SHOW @RECORD, TITLE_CODE
CP1101
CP2264
CP2264
CP2264
FL0099
SP7890
TR1717
TR4198
TR7657
*USE DS BOOKS_DBM2 SHOW @RECORD, TITLE_CODE, ADVANCE_AMT
*EXTRACT
1 CP2264 .00
2 CP3381 .00
3 FL0839 .00
4 FL4948 .00
5 TR1717 .00
6 TR1717 .00
7 TR1717 .00
8 TR7657 500.00
*SELECT WITH NEWBOOK MATCH ON TITLE_CODE IF ADVANCE_AMT=0 SHOW &
@RECORD, IS @RECORD:T, IS, TITLE_CODE, ADVANCE_AMT
1 2 CP2264 .00
1 3 CP2264 .00
1 4 CP2264 .00
5 7 TR1717 .00
*SELECT WITH NEWSTK MATCH ON TITLE_CODE IF ADVANCE_AMT=0 SHOW &
@RECORD, IS,@RECORD:T TITLE_CODE, IS ADVANCE_AMT ADVANCE MASTER
1 2 CP2264 .00
5 7 TR1717 .00
6 7 TR1717 .00
7 7 TR1717 .00
*SELECT WITH NEWSTK MATCH ON TITLE_CODE SHOW @RECORD, IS,@RECORD:T&
TITLE_CODE, IS ADVANCE_AMT ADVANCE BOTH
1 1 CP2264 .00
7 5 TR1717 .00
8 9 TR7657 500.00
NOTES: Using a master Data Index (DI) with ADVANCE TRANSACTION will result in no UNMATCHED:M records. This is because the records are first read in the transaction DS, then looked up via the master DI. Unmatched master records are never read. The SETUP statement, used in Process Modules (PM), has parallel capabilities for advancing matched records. The SETUP statement overrides an ADVANCE clause in the command that calls the PM.
SEE ALSO: MERGE, PURGE, SELECT, and UPDATE commands and SETUP Statement.
PURPOSE: In the commands MERGE, PURGE, SELECT, and UPDATE the after conditional clause serves to further limit records available for processing after the master and transaction conditions have been satisfied and after the records have been matched.
|
condition |
can contain any of the conditionals discussed in Chapter 6, and can compare values in the transaction and master Data Sets (DS). Field names that are identical in master and transaction sets are identified by an optional :M after the master field name and a required :T after the transaction field name. System fields and declared Global Storage (GS) fields can also be tested. |
SELECT WITH BOOKS_DBM2 ON TITLE_CODE IF:A QTY:M > ORDER_AMT:T
SELECT WITH BOOKS_DBM2 ON TITLE_CODE IF:A QTY > ORDER_AMT:T
The two examples have the same effect, since the qualifier: M is optional.
SELECT WITH BOOKS_DBM2 MATCH ON TITLE_CODE &
IF:T TITLE_CODE HAS "CP" UNLESS:A QTY < ORDER_AMT:T
NOTES: When excluding records from processing, ACCENT R follows this sequence:
The master records that are checked against the master conditional clause until one satisfies it. (Conditional Clauses are described in Chapter 6.)
Transaction records are checked against the transaction conditional clause until one satisfies it.
The pair of qualifying records are checked against the MATCH clause.
If there is a match, the IF:A; UNLESS:A clause is tested. If it fails, master and transaction records are processed as specified by the ADVANCE clause until another match is found or an end-of-file condition occurs.
SEE ALSO: MERGE, PURGE, SELECT, and UPDATE
PURPOSE: The IF; UNLESS clause is used in commands to limit the action to particular records. It causes a sequential search of the DS or Data Index (DI) domain. If a WHEN clause is also used, only those records that satisfy the WHEN clause are further examined to see if they satisfy the IF Conditional Clause.
Any conditional clause any place in the command defaults to the master DS unless it is specifically identified as a transaction conditional clause or an after conditional clause. For this reason, no identifier is needed on the master conditional clause. It can, for clarity, be identified by an :M suffix on the introductory IF or UNLESS.
IF[:M]; UNLESS[:M] condition
|
condition |
can contain any of the conditions discussed in Chapter 6. DS, system, and Global Storage (GS) fields can be tested. Only records that satisfy the specified condition are acted upon by the command. |
IF QTY < 100 OR > 500 AND COST = 350
IF @STRING = "CH" OR "ADD"
NOTES: Conditional clauses can be used with all Data Manipulation commands except APPEND.
For conditional clauses that are used with a transaction data set see IF:T and UNLESS:T.
When a DI is declared, the IF; UNLESS clause can be used with the WHEN clause, to further quality records after they satisfy the WHEN clause.
When an IF; UNLESS clause is used in a command that calls a Process Module (PM), the IF; UNLESS clause selects the records that are processed by the BEFORE, DETAILS, and AFTER sections. Records that fail the conditional clause are processed by the UNQUALIFIED section.
Any command that includes an IF; UNLESS clause automatically updates the system fields @QUALIFIED and @UNQUALIFIED. If a command contains more than one IF; UNLESS clause, each record that satisfies any IF; UNLESS clause is counted as qualified.
SEE ALSO: Conditional Clauses
PURPOSE: In the commands MERGE, PURGE, SELECT and UPDATE, the IF:T; UNLESS:T clause restricts action to specific records in the transaction Data Set (DS).
|
condition |
can contain any of the conditionals described in Chapter 6. |
IF:T @RECORD = 375
IF:T TITLE_CODE BEGINS with "TR"
UNLESS:T PAY_TERMS HAS "NET"
NOTES: The conditionals can apply to transaction DS fields, system fields, Global Storage (GS) fields, or constants. However, it cannot apply to master DS fields.
The transaction conditional clause may appear anywhere in the command, but must be identified with a suffix :T on the introductory word of the clause.
If no transaction conditional clause is used, all transaction records are considered for action by the command.
SEE ALSO: MERGE, PURGE, SELECT, and UPDATE
PURPOSE: The MATCH BY clause specifies the field(s) in the transaction Data Set (DS) that are used to retrieve records from the master DS using a RAM Data Index. The match field(s) of the master DS are those specified in the ON \\fields\\ clause of the DOMAIN statement. Only the field(s) specified in the MATCH BY clause are used for retrieval. Full and partial key retrievals are controlled by the completeness of the field list. The MATCH BY clause is valid in the join data manipulation commands: PURGE, SELECT, and UPDATE. See CHANGE for a complete discussion of MATCH BY as it pertains to that command. Use the MATCH ON clause, discussed next, for non-indexed join operations.
/MATCH/ BY \\fields\\
|
fields |
is one or more transaction set field names, separated by commas. The field(s) must be listed in the same order and must be of the same type as the field(s) in the ON \\fields\\ clause of the domain specified for the master DS. If a left subset of the ON \\fields\\ clause is specified, a partial key field retrieval is performed. |
The following examples use the master DS BOOKAUTHOR_DBM2 and a Data Index (DI) BOOKAUTHOR_DBM2, which has a domain on more than one field. The below transaction DS NEWDAT is listed.
*USE DS NEWDAT
*EXTRACT SHOW AUTHOR_CODE, TITLE_CODE
201-86-3791 CP8002
702-61-4439 FL4409
511-45-7008 TR1111
The Schema Definition (SD) and the DI for the master DS are listed below, followed by the complete master DS.
*LIST SD BOOKAUTHOR_DBM2
00100 AUTHOR_CODE,CHAR,11
00110 TITLE_CODE,CHAR,6
00120 AUTHOR_ORDERED,INT,6
00130 ROYALTY_PERCENT,FLOAT,3
*LIST DI BOOKAUTHOR_DBM2
00100 INDEX TYPE IS RAM
00110 INDEX TO BOOKAUTHOR_DBM2
00130 DOMAIN TITLE_CODE ON TITLE_CODE
00150 DOMAIN AUTHOR_CODE ON AUTHOR_CODE
00170 DOMAIN ORDER ON AUTHOR_ORDERED
00190 DOMAIN AUTH_TIT ON AUTHOR_CODE, TITLE_CODE
00200 DUPLICATES NOT ALLOWED
00210 KEY CHANGE NOT ALLOWED
*USE DS BOOK AUTHOR_DBM2 DI BOOK AUTHOR_DBM2
*EXTRACT USE DOMAIN AUTHOR_CODE
201-86-3791 CP8001 12 25
201-86-3791 CP8002 15 25
506-34-2210 FL7743 11 15
511-45-7008 TR1011 9 75
702-61-4439 FL4409 4 50
777-65-3412 SF3223 1 60
The user selects records with the domain AUTH_TIT, which is on author and title codes. The first SELECT command gives MATCH BY fields for both fields in the domain, and selects only records that match on both fields. The second gives a MATCH BY field for only the first field of the domain, and therefore matches only on that field. Note that the second command picks only the first record that matches on the MATCH BY field. Both commands retrieve the records in the order of the transaction DS.
*SELECT WITH NEWDAT USE DOMAIN AUTH_TIT &
MATCH BY AUTHOR_CODE, TITLE_CODE SHOW DATA
201-86-3791 CP8002 12 25
702-61-4439 FL4409 4 50
*SELECT WITH NEWDAT USE DOMAIN AUTH_TIT &
MATCH BY AUTHOR_CODE SHOW DATA
201-86-3791 CP8001 12 25
702-61-4439 FL4409 4 50
511-45-7008 TR1011 9 75
NOTES: When a RAM DI is associated with the master DS in a join command, the MATCH BY \\fields\\ clause, the MATCH ON \\fields\\ clause, or both clauses may be used. The MATCH BY \\fields\\ clause can only be used if a RAM DI is active.
When the MATCH BY \\fields\\ clause is used, ACCENT R sequentially retrieves a transaction DS record. Using its key field value and the RAM DI, a matching master DS record is retrieved. (The ADVANCE MASTER clause instructs ACCENT R to retrieve all master Data Set records with the same key field value as the current transaction DS record. See the ADVANCE clause for more details.) The transaction DS controls the order in which records are retrieved from the master DS. The transaction DS does not have to be sorted. Master records will be retrieved in the order in which the transaction records are processed.
When the MATCH ON \\fields\\ clause is used (which is described next), ACCENT R processes the master DS in the logical order of the specified domain and sequentially retrieves from the transaction DS. The logical order of the specified domain must be the same as the order specified in the MATCH ON \\fields\\ clause. The transaction DS must be sorted in the order specified in the MATCH ON \\fields\\ clause.
When both the MATCH BY \\fields\\ and MATCH ON \\fields\\ clauses are used, ACCENT R retrieves a master DS record using the MATCH BY \\fields\\ clause algorithm, then performs the MATCH ON \\fields\\ clause algorithm. The subset of records retrieved from the master Data Set by the MATCH BY \\fields\\ clause must be sorted on the field(s) specified in the MATCH ON \\fields\\ clause.
SEE ALSO: ADVANCE, MATCH ON clause, and PURGE, SELECT, UPDATE
PURPOSE: The MATCH ON clause is used with the commands PURGE, UPDATE, SELECT and MERGE to specify the fields that provide values to be matched from both the master and transaction Data Sets (DS). MATCH ON is designed primarily for non-indexed retrieval, but can also be used when a Data Index (DI) is declared.
/MATCH/ ON \\fields\\
|
\\fields\\ |
is a list of field names separated by commas. Both the master and transaction DS’s must be sequenced on the match fields. If the fields that are to be matched do not have the same names in the transaction and master sets, they are shown as: master_matchfield_name = transaction_matchfield_name |
MATCH ON PUB_CODE
MATCH ON TITLE_CODE, TYPE_CODE
MATCH ON TITLE_CODE=BOOK_CODE, TITLE_TEXT
In the third example, the master field TITLE_CODE is matched with the transaction fields BOOK_CODE. The second match field is called TITLE_TEXT in both DS’s.
NOTES: When there are duplicate match field values in either the transaction or master DS, the exact manner of matching becomes critical. The ADVANCE clause, discussed earlier in this section, can be used to control the order of matching. The default, if no ADVANCE clause is specified, is for the first matching master record to be processed against all matching transaction records. See diagrams of matching shown for the ADVANCE clause.
Before a master and a transaction DS are used with the MATCH ON clause for non-indexed access, the records in each set must be sorted in order of the match field values.
MATCH ON fields in the master and transaction DS’s must be of the same type and size.
When more than one match field is specified in the MATCH ON clause, ACCENT R processes a master record only when all its match field values are identical to those in a transaction record.
SEE ALSO: ADVANCE, MATCH BY Clauses, and PURGE, SELECT, UPDATE, MERGE Commands
PURPOSE: In the commands SELECT, PURGE and UPDATE the selective target clause directs records to output Data Sets (DS). It can act on matched, unmatched, or unqualified records from either the master or transaction set.
[APPEND] subset TO ds_name
[…[APPEND] subset TO ds_name]
|
adds the selected records onto the end of the output DS. APPEND is optional, without APPEND, the current contents of the output DS is cleared before the selected records can be written to it. |
|
|
subset |
can be any of the following: |
|
for records in the master DS that have matches in the transaction set. The :M suffix is optional. The form in the PURGE command is PURGED/:M/. |
|
|
for records in the transaction DS that match master records. The :T suffix is required. |
|
|
for records in the master DS that do not have matches in the transaction DS. This is the default condition for any unspecified TO ds_name. The UNMATCHED:M clause does not output any records if a MATCH BY clause has been specified. This is because fields in the transaction DS are used as domain values to retrieve records in the master DS. No "unmatched" master DS records are read, so unmatched records are not processed. The form in the PURGE command is /UNPURGED/:M/. |
|
|
for records in the transaction DS that do not match records in the master DS. |
|
|
for records in the master DS that do not satisfy the master conditional clause and the AFTER clause. |
|
|
for records in the transaction DS that do not satisfy the transaction conditional clause and the AFTER clause. |
|
|
specifies the DS to which the selected records are written. |
SELECT WITH BOOKS_TRAN MATCH ON TITLE_CODE&
MATCHED TO BOOKS_DBM2 APPEND UNMATCHED TO NEWBOOKS
NOTES: The output DS must exist in the current Data Base Library (DBL) and must have the same Schema Definition (SD) as the DS from which the records are being taken (transaction DS for transaction records and master DS for master records). The IN DBL clause is not allowed in the selective target clause.
Two or more subsets can be directed to the same output DS. If the master and transaction DS’s have identical SD’s, any or all six subsets could go to the same output DS. If master and transaction DS’s have different SD’s, then any of the DS’s MATCHED:T, UNMATCHED:T, and UNQUALIFIED:T could go to the same output DS, and any of the DS’s MATCHED:M, UNMATCHED:M, and UNQUALIFIED:M could go to the same output DS. Any subsets directed to the same output DS will merge as they are output, although they do not merge with any data existing in the output DS. Therefore, for example, SELECT can be used to do some very special merging operations.
SELECT WITH TRANS ON ID_FIELD UNMATCHED TO UN_MAT UNMATCHED:T TO UN_MAT
This would put a copy of all unmatched master records and unmatched transaction records into the data set UN_MAT. Whether output DS’s is affected by updating done in the Process Module (PM) depends on the command being used and on the PM section in which updating statements occur.
SEE ALSO: PURGE, SELECT, UPDATE, and “Flow of Control in Record Processing Sections”
PURPOSE: The SET clause is used to change values in specified fields when there is some common element to the changes. There can be a list of value assignments, which are separated by commas.
SET \\exp TO field_name\\
|
exp |
can be a field, function, constant, literal, or arithmetic expression of appropriate type for the field being changed. See Chapter 6 for expressions. |
|
is any of the fields in the current Data Set (DS), any system field, or a declared Global Storage (GS) field. |
SET PRICE * 1.25 TO PRICE
SET QTY:T + QTY:M TO QTY:M,LOC:T TO LOC:M
SET @STRING TO COMMENTS
NOTES: When used in one of the Join commands, SET can move values from the transaction DS to the master DS, but not from the master to the transaction. There is no limit to the number of value assignments that can be made within the SET clause.
SET clauses can always be used with conditional clauses to limit the changes to specific records. However, a SET clause acts on all records that satisfy each condition in the clause. There are variations in how the SET clause operates within different commands and with other clauses. This may affect which command is chosen for use.
During CHANGE, interactive input is invoked for each record after the SET clause is utilized. Interactive entry or changes could therefore override and change a value established in a given record by the SET clause.
During ENTER the SET clause is utilized after all fields for the record are entered. Therefore, the SET clause can override entered values. When used with ALTER and UPDATE, the SET clause changes the values in the master DS only, not in output DS’s. When used with all other commands, it changes the values as they are displayed at the terminal or as they are in output DS’s, but does not affect the current master DS. In addition, when used with the SHOWING clause, the SET clause is applied to the records before they are shown or saved. This means that what is shown, with ALTER and UPDATE, is the records in the current DS. With the other commands, what are shown are the records that are going to an output DS.
SEE ALSO: ALTER, CHANGE, CONVERT, DELETE, ENTER, EXTRACT, PURGE, SELECT and UPDATE
PURPOSE: The SHOWING clause displays selected fields at the terminal or saves them to a System File (SF).
SHOW/ING/ {DATA; ALL; \items[@”format”]\} [SAVE [APPEND/ING/] /ON/ table_name]
|
displays the values in all fields of the current DS, separating the fields with one space. The fields are displayed in the order in which they appear in the Schema Definition (SD). The default field separator, a space, can be changed by setting a value to @SEPARATOR. |
|
|
displays the same information as DATA, but also includes the system field @RECORD. |
|
|
\\items[@"format"]\\ |
specifies the list of items to be shown, separated by commas. The item can be a fieldname, expression, or literal. The string literal nB or nS (number of spaces or number of blanks) is particularly useful, since the items named are displayed with no spaces between them. |
|
SAVE [APPEND/ING/] /ON/sf.name |
saves the output on a specified SF (system file). If this option is used, the data will be saved, in the format specified, but will not display at the terminal. If the APPENDING option is specified it adds output to an existing SF. |
SHOW TITLE_CODE,PRICE,ROYALTY IF TITLE_CODE BEGINS WITH "CP"
CP2264 20.0010
CP3681 7.99 5
CP4123 100.9512
If specific fields are requested, with no explicit formatting, they are displayed with no spaces between them. When the fields are full, the display is difficult to read. Below, the same fields are specified, this time with the string literal nB specified to improve readability.
SHOW OBJECT_NO,4B,ON_HAND,2B,ON_ORDER IF OBJECT_NO BEGINS WITH "V"
CP2264 20.00 10
CP3681 7.99 5
CP4123 100.95 12
NOTES: When the SHOWING clause is used with a conditional clause, only qualified records will be displayed or saved.
When the SHOWING clause is used with a SET clause, it will always show values as altered by the SET clause.
In the Join commands, the SHOWING clause can act on data from both master and transaction DS’s. The fields are identified by :M or :T suffixes. If no suffix is used, ACCENT R will first search the master DS for the fields named. Only matching records are affected.
SEE ALSO: ALTER, CHANGE, CONVERT, COUNT, DELETE, EXTRACT, MERGE, PURGE, REPORT, SELECT, SORT, UPDATE, Formats, @SEPARATOR
PURPOSE: The STOP; END clause limits the processing of a command to records preceding or including the first record that satisfies the condition set.
{STOP; END} {IF; UNLESS} condition
|
STOP |
limits action to all records before the first record that satisfies the condition. |
|
END |
limits action to all records up to and including the first record that satisfies the condition. |
|
IF; UNLESS condition |
describes the condition that the STOP; END clause works with. |
This example shows the importance of the order of the records when the STOP; END clause is used. An EXTRACT command first shows the original order of the records in the Data Set (DS). The next EXTRACT command, which includes a STOP conditional, displays all records in the DS from the first one up to the point where the condition is satisfied. The DS is then sorted on the search field, to ensure that all records will be displayed that satisfy the condition. The final EXTRACT command does indeed show all records with a cost of less than $21.
*USE DS BOOKS_DBM2
*EXTRACT SHOW TITLE_CODE,4B,PRICE
CP2264 20.00
CP3681 20.95
FL0839 7.00
FL4948 21.59
TR1717 2.99
TR5139 19.99
6 Record(s) Processed
*EXTRACT STOP IF PRICE > 21
CP2264 20.00
CP3681 20.95
FL0839 7.00
2 Record(s) Processed
*SORT ON PRICE
6 Record(s) Processed
*EXTRACT STOP IF PRICE > 21
TR1717 2.99
FL0839 7.00
TR5139 19.99
CP2264 20.00
CP3681 20.95
5 Record(s) Processed
NOTES: This clause stops processing when it encounters the first record in the DS that satisfies the condition. Therefore, the order of records may be important. If trying to find any record that will satisfy a given condition, the records can be in any order. However, if trying to find all records that satisfy the condition, then the records must be sorted or indexed on the field(s) specified in the condition.
The STOP; END clause takes precedence over all other conditional clauses in effect. It simulates an "end of file" condition. However, it does not change the value in @EOF unless all records of the data set were processed.
When used in PURGE, UPDATE and MERGE commands, this clause can be applied to the transaction Data Set by appending the suffix :T to the IF or UNLESS of the conditional clause, as is done in the IF:T; UNLESS:T clause. It can also be applied to the after matched records by appending the suffix :A to the IF or UNLESS of the conditional clause.
If a STOP or END clause is used in a Join command without a suffix, it defaults to the master Data Set.
STOP; END clauses for both master DS and transaction DS can be used in the same command.
SEE ALSO: ALTER, CHANGE, CHECK, CONVERT, COUNT, DELETE, MERGE, PURGE, EXTRACT, REPORT, SELECT, SORT, UPDATE Conditions
PURPOSE: The TO clause makes it possible to create output Data Sets (DS) containing all RECORDS from the current DS’s, or various subsets thereof.
The acceptable form of the TO clause syntax is command dependent. See the particular command description for the correct form for that command.
[APPEND] TO ds_name [IN DBL clause]
[APPEND] UNDELETED TO ds_name [IN DBL clause]
[APPEND] DELETED TO ds_name [IN DBL clause]
[APPEND] [UNQ/UALIFIED/TO ds_name [IN DBL clause]
[APPEND] MATCHED TO ds_name [IN DBL clause]
[APPEND] UNMATCHED ds_name [IN DBL clause]
NOTES: In general, if the APPEND option is used, the records are appended to the output DS without removing the existing records from the output DS. Without APPEND the output DS is cleared before records are written to it.
PURPOSE: The USE DOMAIN clause specifies the domain of a Data Index (DI) that is to be used for retrieval from a Data Set (DS). This clause overrides the default condition of using the first domain. It is only valid if a Data Index is declared.
/USE/ DOMAIN name
|
name |
can be the name of any domain in the active DI. |
*EXTRACT IF PUB_CODE = 1944 USE DOMAIN TITLE_CODE
In the next example, taken from a Command Module (CM), the domain name is assigned by Dynamic Command Modification.
REPORT VIA MAIL_LABELS IF $@STRING(3)=$@STRING(5) &
USE DOMAIN $@STRING(3)
NOTES: When a DI for a master DS is activated for retrieval, the first domain in the DI is assumed if this clause is not expressed.
SEE ALSO: APPEND, ENTER, LOAD, MERGE, SORT
PURPOSE: The VIA clause is used to specify a Process Module (PM) and to correlate names of auxiliary Data Sets (DS), Data Base Libraries (DBL), and System Files (SF) with parallel designators given in the RELATE statements of the PM.
VIA pm_name [IN DBL dbl_name]
[EQUATE \\{ds_name [IN DBL dbl_name]; dbl_name; di_name}TO designator\\]
[REPORT n [APPEND/ING/] TO sf_name
[…[,] REPORT n [APPEND/ING/] TO sf_name; ..., n [APPEND/ING/] TO sf_name]
|
pm_name |
is the name of a PM that is to be executed by the command. |
|
IN DBL dbl_name |
used to specify the PM is in another DBL. |
|
EQUATE\\ ds_name TO designator\\ |
gives the names of auxiliary DS’s that are to be associated with designators already assigned for a Schema Definition (SD) in a RELATE statement in the PM. Any DS that is associated with the SD can be used. The command must EQUATE a DS for each auxiliary RELATE statement that names a SD instead of a DS. If all auxiliary DS’s are related in the PM by DS name rather than SD name, this clause should not be used. It cannot change an assigned DS, but is used only when a SD is assigned in the RELATE statement. |
|
EQUATE \\dbl_name TO designator\\ |
gives the names of the DBL’s that are to be associated with designators assigned by RELATE DBL FROM COMMAND statements. |
|
EQUATE \\di_name TO designator\\ |
gives the names of auxiliary Data Indexes (DI) that are to be associated with designators already assigned for an Index Definition (ID) in a RELATE statement in the PM. Any DI that is associated with the ID can be used. The command must EQUATE a DI for each auxiliary RELATE statement that names an ID instead of a DI. If all auxiliary DI’s are related in the PM by DI name rather than ID name, this clause should not be used. It cannot change an assigned DI, but is used only when an ID is assigned in the RELATE statement. |
|
REPORT n TO sf_name |
gives the System File (SF) name that is to receive REPORT n. This subclause is optional, since all report files can be named in the RELATE statements in the PM. However, using this subclause in the command allows a given report to be sent to a different SF each time the PM is run. If this clause is used, the RELATE statement for the same report must be: RELATE SF FROM COMMAND AS REPORT n If a report is related in the PM as FROM COMMAND, but a SF is not specified in the command to receive that report, the report will display at the terminal. |
|
REPORT n APPEND/ING/ |
causes new report data to be added to the end of an existing SF. If specifying more than one report on a VIA clause, the word REPORT does not need to be repeated for the second and subsequent reports. If the word REPORT is omitted for the second and subsequent reports, a comma must separate each REPORT TO subclause. If the word REPORT is repeated, the comma is optional. |
*REPORT VIA MO_END EQUATE SALES_DBM2 TO MOSALES REPORT 1 TO MSALE
*UPDATE VIA PUB_CHANGE REPORT 1 TO RELOC,REPORT 2 TO PHOCHG
*EXTRACT… VIA… &
REPORT 1 TO MO_SALE &
REPORT 2 APPEND TO YTD_TOT &
REPORT 3 TO DISC_TOT
In this example, the word REPORT is repeated each time, so a comma is not required to separate each subclause. The data for the second report will be appended to the SF.
*EXTRACT… VIA… &
REPORT 1 TO MO_SALE,&
2 APPEND TO YTD_TOT,&
3 TO DISC_TOT
In this example, the word REPORT is not repeated each time, so a comma must be used to separate each subclause. The data for the second report will be appended to the SF.
SEE ALSO: ALTER, CHANGE, DELETE, ENTER, EXTRACT, LOAD, REPORT, SORT, MERGE, PURGE, SELECT, UPDATE “GET Statement for DBL Access”
PURPOSE: The WHEN clause is a special conditional clause used to specify key field values for record retrieval. The WHEN clause is much more efficient than the IF; UNLESS conditional clause, since it examines the data stored in the data sets index and only records satisfying the WHEN clause are read. This clause can be used with any simple Data Manipulation command when a Data Index (DI) is declared. It cannot be used in a command that requires a transaction Data Set (DS).
WHEN \\ /field_list/ [operator] search_key\\
|
field_list |
specifies the key fields that will be used for retrieval. The form of field_list is: field [;field…] The fields are separated by semicolons (;) and must be a left subset of the current domain’s key field list. This list is optional and if not specified a left subset of the current domain’s key field list is assumed. |
|
operator |
specifies the relational operation to be applied. The valid operators are: = or EQ equal to < or LT less than > or GT greater than < = or LE less than or equal to = > or GE greater than or equal to The default value, if no operator is specified, is EQ (=). |
|
search_key |
can be a value list or a value range. value list consists of one or more literals or constants, separated by semi-colons. Values must match, from left to right, a left subset of the current domain. This form selects records that match on all the key values given. The form is: val[;val…] value range specifies key field values for the first and last record of a range. The form is: val[;val…] range indicator [val[;val...] |
|
|
An inclusive (includes the second value list) or exclusive (excludes the second value list) range indicator can be specified. Valid range indicators are: THROUGH inclusive range THRU inclusive range colon (:) inclusive range UPTO exclusive range Values must be literals or constants and are separated by semicolons. |
|
|
The key value range selects a group of records, beginning with the record whose key fields match the values on the left of the indicator (or the record with the next higher values), and ending with the record whose key fields match the values to the right of the indicator (or the record with the next lower values). When a range is used, only the operators EQ (=), GT (>), or GE (=>) can be used. The operator determines whether the group of records selected begins with the record that matches the left-hand value list or the record with the next higher values. |
The following are examples of the WHEN clause.
WHEN STATE = "CA", STATE = "NV",STATE = "WA"
WHEN STATE = "CA", "NV","WA"
WHEN "CA","NV" "WA"
The above three clauses are equal in effect. They all retrieve records in which the field STATE contains "CA", "NV" or "WA".
WHEN INT EQ 5 THRU 9
This clause retrieves all records whose key field value is greater than or equal to 5 and less than or equal to 9.
WHEN INT GT 5 UPTO 9
This clause retrieves all records whose key field value is greater than 5 and less than 9.
WHEN INT;ALPH EQ 2;"TWO"
This clause retrieves all records in which the field INT is 2 and the field ALPH is “TWO”.
WHEN INT;REAL EQ 2;2.2 THRU 4;4.4
This clause retrieves a group of records beginning with the first record in which INT = 2 and REAL = 2.2 and ending with the last record in which INT = 4 and REAL = 4.4. All records, in domain sequence, between 2;2.2 and 4;4.4 are retrieved.
Note that a record with values such as INT = 2 and REAL = 13.4 is within the specified range and will be retrieved. This is because the records are index on INT as the primary key and REAL as the secondary key. A diagram of this index is shown following.
|
INDEX |
|
|
INT |
REAL |
|
2 |
2.2 |
|
2 |
13.4 |
|
3 |
3.3 |
|
3 |
8.7 |
|
4 |
4.4 |
|
4 |
4.5 |
|
5 |
5.1 |
An example of this WHEN clause is shown below, where records between the specified range of 2;2.2 and 4;4.4 are extracted.
*EXTRACT WHEN INT;REAL EQ 2;2.2 THRU 4;4.4
2 2.2
2 13.4
3 3.3
3 8.7
4 4.4
Compare this example with the next example.
WHEN INT EQ 2 THRU 4 IF REAL BETWEEN 2.2 AND 4.4
When the WHEN clause is combined with an IF clause, the WHEN clause is applied first. Records that satisfy the WHEN condition are then retrieved individually and tested against the IF clause. In this case, only records that satisfies both conditions INT EQ 2 THRU 4 and REAL IS BETWEEN 2.2 AND 4.4 will be extracted.
*EXTRACT WHEN INT EQ 2 THRU 4 IF REAL BETWEEN 2.2 AND 4.4
2 2.2
3 3.3
4 4.4
There is another difference between the IF; UNLESS and WHEN clauses that should be emphasized. It deals with complex IF; UNLESS and WHEN clauses that use commas to separate the individual expressions in the clause.
The comma in complex IF; UNLESS clauses simply functions like an OR logical operator. Hence, IF INT EQ 5,5 is equivalent to IF INT EQ 5 OR INT EQ 5.
*EXTRACT IF INT = 5, 5
5 5.1
On the other hand, the commas in a complex WHEN clause separates expressions that are independent of each other and are executed individually. Hence, WHEN INT EQ 5,5 is equivalent to executing WHEN INT EQ 5 twice. In this case, there exists the possibility that a complex WHEN clause will retrieve a record more than once. This usually occurs when the search keys in the clause (5,5) are entered as variables that happen to evaluate to the same value.
Compare the next WHEN clause with the preceding IF clause.
*EXTRACT WHEN INT = 5, 5
5 5.1
5 5.1
Similarly, when a complex WHEN clause retrieves records in two ranges that overlap, the records in the intersection will be retrieved twice. In the next example, records in the intersection (INT = 3) are retrieved twice.
*EXTRACT WHEN INT = 2 THRU 3, 3 THRU 4
2 2.2
2 13.4
3 3.3
3 8.7
3 3.3
3 8.7
4 4.4
4 4.5
The following examples are all based on the DS BOOKS_DBM2 and a DI BOOKS_DBM2, which has a domain on more than one field. The Schema Definition (SD) is listed below, then the Data Set (DS) in non-indexed sequence, then the Data Index (DI).
*LIST SD BOOKS_DBM2
00100 TITLE_CODE,CHAR,4
00105 TITLE_TEXT,CHAR,40
00110 TYPE_CODE,CHAR,12
00120 PUB_CODE,CHAR,4
00130 PRICE,CHAR,20
00140 ADVANCE_AMT
00150 ROYALTY,FLOAT,3
00160 YTD_SALES,FLOAT,10,2
00170 NOTES,CHAR,200
00180 PUB_DATE,FULLDATE
*USE DS BOOKS_DBM2
*EXTRACT SHOWING TITLE_CODE,2B,TYPE_CODE,2B,PUB_CODE,2B,PRICE
CP2264 computer 1944 20.00
CP3681 computer 1227 20.95
CP3896 computer 1030 19.99
FL0839 for_lang 1030 7.00
FL1757 for_lang 1944 11.95
TR1717 travel 1227 2.99
TR7657 travel 1227 9.99
*LIST DI BOOKS_DBM2
00100 INDEX TYPE IS RAM
00110 INDEX TO BOOKS_DBM2
00120 !
00130 DOMAIN TITLE_CODE ON TITLE_CODE
00131 DUPLICATES NOT ALLOWED
00132 KEY CHANGE NOT ALLOWED
00140 !
00150 DOMAIN TYPE_CODE ON TYPE_CODE
00160 !
00170 DOMAIN PUB_CODE ON PUB_CODE
00180 !
00190 DOMAIN PRICE ON PRICE
*EXTRACT USE DOMAIN TITLE_CODE WHEN PRICE GT 15 SHOWING, &
TITLE_CODE 2B,TYPE_CODE,2B,PUB_CODE,2B,PRICE
CP2264 computer 1944 20.00
CP3681 computer 1227 20.95
CP3896 computer 1030 19.99
FL1757 for_lang 1944 11.95
The following two commands both lists all records for the TYPE_CODE of “computer.” However, note that they are ordered differently, depending on the domain in effect. The first command orders the records on TITLE_CODE, whereas the second orders them on PUB_CODE.
*EXTRACT USE DOMAIN TITLE_CODE WHEN TYPE_CODE="computer" SHOWING &
TITLE_CODE,2B,TYPE_CODE,2B,PUB_CODE,2B,PRICE
CP2264 computer 1944 20.00
CP3681 computer 1227 20.95
CP3896 computer 1030 19.99
*EXTRACT USE DOMAIN PUB_CODE WHEN TYPE_CODE="computer" SHOWING &
TITLE_CODE,2B,TYPE_CODE,2B,PUB_CODE, 2B, PRICE
CP3681 computer 1227 20.95
CP3896 computer 1030 19.99
CP2264 computer 1944 20.00
The next example gives more than one key.
*EXTRACT USE DOMAIN PRICE WHEN TYPE_CODE="for_lang", "travel" &
SHOWING TITLE_CODE,2B,TYPE_CODE,2B,PUB_CODE,2B,PRICE
TR1717 travel 1227 2.99
FL0839 for_lang 1030 7.00
TR7657 travel 1227 9.99
FL1757 for_lang 1944 11.95
The last example supplies values for each field in the domain. The two keys are separated by a comma, and within each key, the values are separated by semicolons.
*EXTRACT USE DOMAIN TITLE_CODE WHEN TYPE_CODE;PUB_CODE=&
"computer";1030,"travel";1227
CP3896 computer 1030 19.99
TR1717 travel 1227 2.99
TR7657 travel 1227 9.99
NOTES: A WHEN clause can be combined with the IF; UNLESS conditional clause in a command. ACCENT R first traverses the current domain for key field values that satisfy the WHEN clause. When a key field value is found that satisfies this clause, ACCENT R reads the record from the DS and checks it against the IF; UNLESS clause.
If a Process Module (PM) is declared, only those records that pass the WHEN clause are acted on by the PM.
WHEN performs some field type conversion. ACCENT R internally converts an integer or binary integer value to a FLOAT data type, and likewise a REAL value to FLOAT. If an indexed field is defined as either FLOAT or binary numeric, the following clause converts the integer 1 and executes.
WHEN nfield EQ 1
However, a conversion will not work for FLOAT or binary numeric values to integers. If an indexed field is defined as either I or BI, the following clause returns an error.
WHEN ifield EQ 1.234
The WHEN clause also permits system fields and Global Storage (GS) fields to be used for the search key. When a system field name or GS field name is entered, its current value is stored as the value of the field being entered. If the system or GS field is subscript, then the subscripts must be a literal or a constant: @INTEGER (5) is allowed, but @INTEGER (%FS_FIELD) is not.
To use a date in the WHEN clause, the date string must be converted to the proper date form with @CAL function and set it to system field @VDATE, then use @VDATE in the WHEN clause.
*SET @CAL "12/31/85" TO @VDATE
*EXTRACT WHEN ORDER_DATE < @VDATE
PURPOSE: In Join commands, the WITH clause specifies the name of the transaction Data Set (DS) to be used.
b tds_name [IN DBL dbl-name]
|
tds_name |
is the name of the transaction DS. |
|
IN DBL dbl_name |
is used to declare a transaction DS from another Data Base Library (DBL). |
*MERGE WITH AUTHORS_DBM2 MATCH ON AUTHOR_LNAME
NOTES: The transaction DS used with MERGE must have the same Schema Definition (SD) as the master DS.
For the other Join commands, the SD’s of master and transaction DS’s do not have to be identical; the match fields may have different names but must be alike in type and size.
SEE ALSO: MERGE, PURGE, SELECT, UPDATE
