FIND / GET WITHIN SET Using SORTKEY

Commands and Syntax › FIND / GET WITHIN SET Using SORTKEY

FIND / GET WITHIN SET Using SORTKEY

FIND/GET WITHIN SET using SORTKEY retrieves member records in sorted database sets based on a specified sort key.

Access mode: The syntax below is invalid when the access switch is set to IDMS.

FIND locates database records but does not retrieve them into the report file. GET locates database records and does retrieve them into the report file.

Syntax:

►►─┬─ FINd ─┬─┬──────────────────────────────┬────────────────────────────────►
   └─ GET ──┘ ├─ FIRst ◄ ─┬────────────────┬─┤
              │           └─ record-count ─┘ │
              ├─ NEXt ─┬────────────────┬────┤
              │        └─ record-count ─┘    │
              └─ ALL ────────────────────────┘

 ►─┬────────────┬─ member-record-name ─┬────────────────┬─ records ───────────►
   └─ view-id. ─┘                      └─ (field-list) ─┘

 ►─┬───────────────────────────────────────────────────────────────┬──────────►
   └─ BELonging to this ─┬────────────┬─ owner-record-name record ─┘
                         └─ view-id. ─┘

 ►─┬──────────────────────────────┬───────────────────────────────────────────►
   └─ withIN sorted-set-name set ─┘

 ►─── WHEre ─┬─┬─ SORtkey ─────────┬─ = ─┬─ sort-key-value ───────────┬─┬─────►
             │ └─ sort-field-name ─┘     └─┬───────────┬─ field-name ─┘ │
             │                             └─ record. ─┘                │
             │             ┌──────────── , ───────────┐                 │
             └─ SORtkey = -▼- concatenated-key-value ─┴─────────────────┘

 ►─┬────────────────┬─────────────────────────────────────────────────────────►◄
   └─ AND criteria ─┘
►►─── REPeat with ─┬────────────────────────────┬─────────────────────────────►
                   ├─ FIRst ─┬────────────────┬─┤
                   │         └─ record-count ─┘ │
                   ├─ NEXt ─┬────────────────┬──┤
                   │        └─ record-count ─┘  │
                   └─ ALL ──────────────────────┘

 ►─┬────────────┬─ member-record-name ─┬────────────────┬─ records ───────────►
   └─ view-id. ─┘                      └─ (field-list) ─┘

 ►─┬──────────────────────────────────────────────────────────────────────────►─
   └─ WHEre ─┬─┬─ SORtkey ─────────┬─ = ─┬─ sort-key-value ───────────┬─┬─────
             │ └─ sort-field-name ─┘     └─┬───────────┬─ field-name ─┘ │
             │                             └─ record. ─┘                │
             │             ┌──────────── , ───────────┐                 │
             └─ SORtkey = -▼- concatenated-key-value ─┴─────────────────┘

─►────────────────────┬───────────────────────────────────────────────────────►◄
  ─┬────────────────┬─┘
   └─ AND criteria ─┘

Expansion of record

►►─┬────────────┬─ record-name ─┬────────────────┬────────────────────────────►◄
   └─ view-id. ─┘               └─ (occurrence) ─┘

Parameters:

FIRST record-count

Retrieves the first n (where n defaults to 1) records in the current occurrence of the set type with the specified sort key value.

NEXT record-count

Retrieves the next n (where n defaults to 1) records in the current occurrence of the set type with the specified sort key value.

Note: Records with the same sort key (duplicates) can be retrieved by specifying the REPEAT WITH NEXT command without changing the preceding sort key value. If retrieval is requested for a record with a different sort key value, use the GET FIRST form of the command.

ALL

Retrieves all records in the current occurrence of the set type with the specified sort key value.

view-id

The qualifying ID for the record or logical record name. Use view-id when you are signed on to multiple subschemas. View-id refers to the subschema where the record (or logical record) can be found.

member-record-name

The member record type in the set (required).

(field-list)—The fields within record-name to be stored in the report file.

Field-list must be enclosed in parentheses.

BELONGING TO THIS owner-record-name

Specifies the owner record type in the set. This clause is required only as needed to resolve ambiguity.

WITHIN sorted-set-name

Specifies the set type. This clause is required only as needed to resolve ambiguity.

WHERE

Supplies the criteria for selecting records:

SORTKEY=—The sort key used for record retrieval.
sort-field-name=—The name of the sort key field used for record retrieval.
Note: If the sortkey is defined as more than one field, the SORTKEY keyword should be used rather than a list of the individual field names.
sort-key-value—The sort key value of the member record retrieved. In the event of duplicate sort keys, the order of retrieval depends on the DUPLICATES specification for the set type.

If the sort key is a group item, all subordinate fields must be specified.

record—The qualifying record name for a field-name. Expanded syntax can be found following the REPEAT parameter.
field-name—The name of the field containing the sort key value.
SORTKEY=concatenated-key-value—A concatenated sort key value used for record retrieval.
In the concatenated-key-value, separate each of the partial key values with blanks or commas.

AND criteria

Specifies criteria used in selecting a record occurrence.

REPEAT

REPEAT GET WITHIN SET using SORTKEY duplicates an immediately preceding FIND/GET WITHIN SET using SORTKEY (or REPEAT for FIND/GET WITHIN SET using SORTKEY) command.

FIRST record-count

Retrieves the first n (where n defaults to 1) records in the current set with the specified sort key value.

NEXT record-count

Retrieves the next n (where n defaults to 1) records in the current set with the specified sort key value.

Note: You can retrieve records with duplicate sort keys by specifying the REPEAT WITH NEXT command without changing the preceding sort key value. If you request retrieval for a record with a different sort key value, use the GET FIRST form of the command.

ALL

Retrieves all records in the current set with the specified sort key value.

view-id

member-record-name

The member record type (required).

(field-list)—The fields within record-name. Enclose field-list in parentheses.

WHERE

Specifies criteria used in selecting records.

SORTKEY=—The sort key used for record retrieval.
sort-field-name=—The sort key field used for record retrieval.
Note: If the sortkey is defined as more than one field, the SORTKEY keyword should be used rather than a list of the individual field names.
sort-key-value—The sort key value of the member record retrieved. In the event of duplicate sort keys, the order of retrieval depends on the DUPLICATES specification for the set type.

If the sort key is a group item, all subordinate fields must be specified.

record—The qualifying record name for a field-name. The expanded syntax can be found below.
field-name—The field name containing the sort key value.
SORTKEY= concatenated-key-value—A concatenated sort key value used for record retrieval.

Separate each of the partial key values with blanks or commas.

AND criteria

Specifies criteria used in selecting record occurrences.

record

The qualifying record name for a field-name.

view-id—The qualifying ID for the record or logical record name. Use view-id when you are signed on to multiple subschemas. View-id refers to the subschema where the record (or logical record) can be found.
record-name (occurrence)—The record in the path where the field-name occurs.
If the field-name occurs in more than one record, use record-name.
If the record occurs more than once in the same path, use occurrence. If a database record name is the same as a CA OLQ keyword, the name should be enclosed in quotation marks.

Considerations:

If criteria is respecified for the REPEAT command, the original selection criteria clause (including the sort key value) is replaced and a REPEAT WITHIN SET command is executed without an implied SORTKEY clause. Therefore, if the selection criteria have been changed and sort key retrieval optimization is preferred, the sort key value must always be used in a REPEAT command.

Examples:

The following examples illustrate the use of the FIND/GET WITHIN SET using SORTKEY and associated REPEAT commands, based on the set occurrence diagram shown below:

Illustrates the use of FIND/GET WITHIN SET and associated REPEAT commands based on the set occurrence diagram

Get Using Storage Key

A GET using storage key command is used to enter the database and establish currency for the SKILL record:

get first skill where calckey = 2080

 SKILL
   SKILL-DBKEY      :    0/5007116:1
   SKILL-ID-0455    :   2080
   SKILL-NAME-0455  : 'RPGII       '
   SKILL-DESCRIPTION-0455 :  '           '
 END OF RECORD

Get Within Set

A GET WITHIN SET using SORTKEY command retrieves the first EXPERTISE record with a sort key value of 03:

get first expertise in skill-expertise set
 where sortkey = 03

 EXPERTISE
   EXPERTISE-DBKEY  :    0/5007055:17
   SKILL-LEVEL-0425 : '03'
   EXPERTISE-DATE-0425 :
   EXPERTISE-YEAR-0425 :   70
   EXPERTISE-MONTH-0425 :   10
   EXPERTISE-DAY-0425 :   10
 END OF RECORD

Repeat With First

A REPEAT command retrieves the next EXPERTISE record for which the EXPERTISE-YEAR-0425 field value is greater than or equal to 70:

repeat with first expertise
 where expertise-year-0425 ge 70

 EXPERTISE
   EXPERTISE-DBKEY  :    0/5007026:6
   SKILL-LEVEL-0425 : '04'
   EXPERTISE-DATE-0425 :
   EXPERTISE-YEAR-0425 :   72
   EXPERTISE-MONTH-0425 :    1
   EXPERTISE-DAY-0425 :   28
 END OF RECORD

Repeat

A REPEAT command specifies the 03 sort key again, to achieve sort-key optimization:

repeat with first expertise where sortkey = 03
and expertise-year-0425 ge 70

 EXPERTISE
    EXPERTISE-DBKEY  :    0/5007055:17
   SKILL-LEVEL-0425 : '03'
   EXPERTISE-DATE-0425 :
    EXPERTISE-YEAR-0425 :   70
    EXPERTISE-MONTH-0425 :   10
    EXPERTISE-DAY-0425 :   10
 END OF RECORD

For more information:

Coding Considerations

Global Syntax