INCLUDE OMIT Substring Comparison using SORT

You can use the INCLUDE OMIT Substring Comparison statement in conjunction with other options to select/omit the number of records to be processed, which can reduce processor and data transfer time. There are two types of INCLUDE OMIT Substring Comparison tests:

• Find a constant within a field value: For example, search the value in a six-byte field for character constant C’OK’. The relation condition is true if the field value is C’**OK**’ or C’****OK’; false if the field value is C’**ERR*’.
• Find a field value within a constant: For example, search character constant C’J69,L92,J82’ for the value in a three-byte field. The relational condition is true if the field value is C’J69’, C’L92’, or C’J82’; false if the field value is C’X24’. Notice the comma is used within the constant to separate the valid three-character values; any character not appearing in the field value can be used as a separator in the constant.

This information is also applicable to OMIT control statement.

INCLUDE OMIT Substring Comparison Syntax

SyntaxINCLUDE COND=(Field-1 starting position, Field-1 length, SS, Relational Operator, Substring Constant)

(P1,M1,SS,EQ/NE,constant) or (P1,M1,EQ/NE,constant)

P1 – specifies the first byte of the character input field. The first data byte of a fixed-length is relative position 1. The first data byte of a variable-length record is relative position 5; the first four bytes contain the record descriptor word. All fields must start on a byte boundary and must not extend beyond byte 4092.

M1 – Specifies the length of the field to be tested. Length is 1 to 256 bytes.

Constant – A character string or a hexadecimal string.

• If m1 greater than constant length: The field value is searched for the constant, and Condition is true if a match is found when the EQ comparison operator is specified, or Condition is true if no match found when the NE comparison operator is specified.
• If m1 is smaller than the constant length: The constant is searched for the field value constant, and Condition is true if match found with EQ comparison operator specified, or Condition is true if no match found when NE comparison operator specified.

Character String Format

Format for a character string constant is C’cc…c’ where ‘c’ may be any EBCDIC character, up to 256 characters. To include a single apostrophe in the character string, such as for the name O’Malley, specify it with two single apostrophes. For example, C’O’ ‘Malley’

Hexadecimal String Format

The format for hexadecimal is X’ xx…xx’ where ‘xx’ is any pair of hexadecimal digits, up to 256 pairs. Valid hexadecimal digits are 0–9, A, B, C, D, E, and F.

INCLUDE OMIT Substring Comparison Examples

Example-01

Suppose you want to select only records having OK in anywhere within bytes 11 through 16 or OR bytes 21 through 23 containing J69, L92, or J92. You can use one of the substring search capabilities of INCLUDE and OMIT to write the statement in a simpler form as:

INCLUDE FORMAT=SS,COND=(11,6,EQ,C'OK',OR,21,3,EQ,C'J69,L92,J82')

Example-02

Suppose you want to select only the books for the Biology, History, Business and Psychology departments. You can use one of the substring search capabilities of INCLUDE and OMIT to write the statement in a simpler form as:

INCLUDE COND=(106,5,SS,EQ,C'BIOL ,HIST ,BUSIN,PSYCH')

With substring search (SS format), you only write the field once and write the character constant so it includes all of the strings you want to search for. If the value in the field matches any of the strings (for example, “BUSIN”), the record will be included. If the value in the field does not match any of the strings, the record will be omitted.

The length of each string must match the length of the field. Because the Department field is 5 characters, you must add a blank at the end of “BIOL” and “HIST”, which are each four characters, but not for “BUSIN” and “PSYCH”, which are each five characters.

SYNCSORT Manual: Click Here