Purpose: The SELECT command retrieves records from a master Data Set (DS) by matching values in "match fields" against values in the "match fields" of a transaction DS. The records considered for retrieval can be limited in each DS, and there are several options for sending resulting subsets of records to the terminal, to other DS’s, or to reports. The select command requires you to specify either a VIA clause, a TO clause or a SHOWING clause.
SELECT WITH clause
(MATCH BY clause; MATCH ON clause)
(VIA clause; SHOWING clause [SAVE clause]; selective target clause)
[IF/:M/; UNLESS/:M/ clause]
[IF:T; UNLESS:T clause]
[IF:A; UNLESS:A clause]
[ADVANCE clause]
[SET clause]
[STOP; END clause]
[/USE/ DOMAIN clause]
[TO clause]
|
WITH clause |
described in detail in Chapter 7. |
|
MATCH BY clause |
described in detail in Chapter 7. |
|
MATCH ON clause |
described in detail in Chapter 7. |
|
VIA clause |
described in detail in Chapter 7. |
|
SHOWING clause [SAVE clause] |
is executed only for matched records after the SET clause has been applied. Both master and transaction records can be shown. Described in detail in Chapter 7. |
|
selective target clause |
described in detail in Chapter 7. |
|
IF/:M/; UNLESS/:M/ clause |
described in detail in Chapter 7. |
|
IF:T; UNLESS:T clause |
described in detail in Chapter 7. |
|
IF:A; UNLESS:A clause |
described in detail in Chapter 7. |
|
ADVANCE clause |
described in detail in Chapter 7. |
|
SET clause |
affects only the matched master records and has no effect on records in the current DS. Described in detail in Chapter 7. |
|
STOP; END clause |
described in detail in Chapter 7. |
|
/USE/ DOMAIN clause |
specifies the domain of a Data Index (DI) to be used for the master DS. Described in detail in Chapter 7. |
In the example below, NEWINV is the transaction DS; TITLE_CODE is the match field and appears in both the BOOKS and NEWINV DS records. SELECT displays a BOOKS record when its TITLE_CODE value is the same as a TITLE_CODE value in a NEWINV record.
*USE DS BOOKS
*SELECT WITH NEWINV MATCH ON TITLE_CODE SHOWING TITLE_CODE, 1B,PRICE
The next command considers only master records with YTD_SALES values other than zero, in selecting master records that contain the same TITLE_CODE value as a NEWINV record. Records are copied to two output DS’s: matched master records to BKOUT and matched transaction records to INVOUT.
*SELECT UNLESS:M YTD_SALES=O WITH NEWINV MATCH ON TITLE_CODE &
MATCHED TO BKOUT MATCHED:T TO INVOUT
Similarly, the next command considers only master records with non-blank NOTES fields and transaction records whose PUB_DATE fields specify the current date. The unmatched master records are copied to DS NOBOOK, and the transaction records that do not contain the current date are appended to NOINV.
*SELECT UNLESS:M NOTES=" "WITH NEWINV IF:T &
PUB_DATE=@DATE MATCH ON TITLE_CODE UNMATCHED:M &
TO NOBOOK UNQUALIFIED:T APPEND TO NOINV
The next command presents all master and transaction records to the record processing sections of a PM. The PM statements can further screen them, creating several output DS’s, manipulate the records, or create reports.
*SELECT WITH NEWINV MATCH ON TITLE_CODE VIA WORK1
The following example assumes a DI is declared for the current data set and uses a MATCH BY clause to select records. In this example only the master records that match transaction records are processed. Each transaction record is read and the match key field value is used to retrieve master records for processing.
*SELECT WITH NEWINV USE DOMAIN PUB_CODE MATCH BY TITLE_CODE SHOW DATA
SEE ALSO: EXTRACT, SUMMARIZE, OSQL SELECT