FILTER
9.4
The FILTER instruction defines a filtering string that contains an expression of conditions used when any read operation is executed for a FILE, AFILE, or IFILE variable. The filtering expression is only used when the FILE, AFILE, or IFILE variable is opened or prepared using the VIEW keyword. The instruction uses the following formats:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Where:
label
Optional. A Program Execution Label.
file
Required. A previously defined and opened FILE, IFILE, or AFILE variable.
sep1
Required. A comma or one of the following prepositions: BY, TO, OF, FROM, USING, WITH, IN, or INTO.
filter
Required. A previously defined Character String Variable that contains the expression of access conditions to be applied when the file variable is being read.
flagvalue
Optional. A previously defined Numeric Variable or decimal number whose value is a bit mask that allows specialized behavior for the FILTER instruction. The {flagvalue} requires the keyword named 'FLAGS'.
skipcount
Optional. A previously defined Numeric variable or decimal number whose value specifies the number ISI keys to be skipped by an IFILE READ, READKS, or READKP instruction when the {file} is an IFILE.
Flags Affected: none
Note the following:
The record filter query string consists of one or more expressions separated by logical operators.
The filter is limited to 4,096 bytes. A longer filter will result in an I85 subcode 103 error.
An expression consists of a field name, an operator, and a value. The operators supported are =, <>, <, <=, >, >= and LIKE (as in SQL syntax). The values can be either a quoted string for a string match, or a numeric value for a numeric comparison.
The logical operators are OR, AND, and NOT.
The record filter expressions can include the unary operators named NOCASE and OVER. These operators are defined as follows:
NOCASE
This unary operator causes all string comparisons to be case insensitive.
Example:
In this case, the string comparison for the CompanyName field is not case sensitive.
OVER
This unary operator sets the PLB OVER flag to TRUE when the affected expression evaluates as FALSE. The intended use of the OVER unary operator is to prevent unnecessary file io actions while filtering records.
Example for usage:
In this case, the PLB program OVER flag is set to TRUE when the CustID field does not equal the 'PICAF' string value.
The record filter expressions may include the operators named SKIPKEYS or SKIPONCE that allow ISI keys to be skipped when executing IFILE READ, READKS, or READKP instructions. The operators can either be specified using the keyword options or by embedding the operator names directly into the filter expression. These keywords are mutually exclusive and a compilation error is generated if both of these keywords are in the same FILTER instruction.
When the SKIPKEYS or SKIPONCE operator string names are specified in the filter string, they must be the first operator. In addition, the embedded sting name operators should not be used when the operator keywords are being used.or an execution error occurs.
$SKIPKEYS[nnn]
$SKIPONCE$[nnn]
The basic operation of the SKIPKEYS and the SKIPONCE operators are identical except the SKIPONCE operator is only executed once for the first read instruction executed after the FILTER is executed. The intended use of these operators is to specify the number of ISI keys that are to be skipped when an IFILE READ, READKS, or READKP instruction is executed.
The {skipcount} value has a range from 0 to 65353. Otherwise, an I85 subcode 131 occurs.
The SKIPKEYS and SKIPONCE operators can only be executed for an IFILE file variable. Otherwise, an I85 subcode 132 error occurs.
The use of the SKIPKEYS/SKIPONCE operators allows very specialized behavior as the runtime processes the keys in an ISI file. The PLB read instruction skips the specified number of ISI keys before the read results are returned to the user application. The use of these operators can give improved performance by minimizing the number PLB instructions when the ISI key structure is known. The improved performance can be more obvious when the Data Manager is being used.
The {file} variable skip count is always initially zeroed when a FILTER instruction is executed.
When the SKIPKEYS skip count is used, the skip count remains in affect for all IFILE read operations until another FILTER instruction resets the skip count. This gives the PLB program the ability to set and clear the SKIPKEYS count as required.
The SKIPONCE skip count executes exactly like the SKIPKEYS operator except the SKIPONCE is only executed once for the next ISAM read operation after the FILTER execution.
When the $SKIPKEYS$ or $SKIPONCE$ string keys are used, they must be specified without any embedded blank characters.
Expressions can be grouped by using the ( and ) characters.
The LIKE operator performs a pattern matching string comparison where an underscore (_) matches any one character, a % matches zero or more characters, and the backslash (\) is the forcing character. For non-LIKE operations, the backslash (\) character is the forcing character but it only forces the single quote (') character, the double (") character, and the blackslash (\) character itself.
The {flagvalue} bit mask value does not currently have any bit mask values for specialized FILTER instruction behavior.
The FILTER instruction expressions allow the data from a left operand that is a column/field name to be compared as a string to the data from a right operand that is a column/field name (9.6). In this case, the data from the right operand is retrieved and a string comparison is performed to the data of the left operand up to the size of the smallest operand. String comparisons are performed as follows:
The '=' operator can only evaluate to a true state when the size of the data for the left and right operands are the same and the data for the operands is the same.
When the left and right operands have the same size, the true state for the '<' and '>' operators are evaluated by a character to character comparison of the data in the two operands.
If the size of the left operand is less than the size of the right operand and the data in the two operands is the same, a '<' operator comparison evaluates to a true state.
If the size of the left operand is greater than the size of the right operand and the data in the two operands is the same, a '>' operator comparison evaluates to a true state.
The NOCASE operator eliminates case sensitivity when comparing each character of the left operand to the characters in the right operand.
Example:
|
|
|
|
|
|
|
|
|
|
|
|
This read returns a record where the PRICESOLD field in a record does not match the STANDARDPRICE field in the same record.
|
|
|
|
An AFILE, FILE, or IFILE read operation may be aborted when a FILTER is being used (9.6A). This allows an end-user to abort a read that is taking an excessively long time before finding a record. By default, the runtimes do not allow a READ to be aborted. This ability is enabled using a keyword named PLB_IOCANCELCHAR keyword in a runtime INI file or by using the SETMODE *IOCANCELCHAR instruction. If a read is aborted by using the IO cancel character/key, the OVER flag is set. The GETFILE CANCELFLAG keyword retrieves a flag that indicates whether the read was abortedt. Keywords for the AFILE, FILE, and IFILE OPEN and PREPARE instructions allow individual file variables to invoke the use of the IO Cancel character support.
The IO Cancel support must be enabled before the OPEN/PREP action of a AFILE, FILE, or IFILE variable. When file variables are opened or prepared, the current IO cancel support is enabled for the file variables. Changing the runtime IO cancel read default settings after a file variable is opened does not change the IO cancel support used by the opened file variable.
The CHAIN instruction resets to current program IO cancel character value back to the default setting specified by the PLB_IOCANCELCHAR INI keyword.
When a FILTER is being applied to a file variable, the IO cancel support affects the file variable read operations.
For more information on PLB VIEW usage, see the following: topics:
See Also: Example Code, Disk I/O Instructions
|
|