Filter String Formatting Rules and Examples

FILTER-STRING is an alpha field that must be parsed by the database filter APIs; therefore the contents must be in a "well-known" format. The criteria for that format are:

  • Each condition must start with a left parenthesis "(" character.

  • Each condition must end with a right parenthesis ")" character.

  • Valid comparison operators include: =, <, >, <=, >=, <>, !=, LIKE, and NOT LIKE. <> and != are "not equal to." LIKE and NOT LIKE are used for pattern matching in alpha fields. For pattern matching, you can use the percent sign (%) to represent any character zero or more times, and the underscore (_) to match any character exactly one time. Pattern matching is case sensitive. For example:

    LIKE 'SM%' matches values of SM, SMITH, SMYTH, SMORES, but not Smith

    LIKE 'A%C' matches values of AC, ABC, ABDEFC, ACIDIC

    LIKE 'A_C' matches values of ABC, ABCDEF, and ARCTIC, but not AC or ACDEFC

  • If a literal value is provided, it must be enclosed in single quotes. (The use of double quotes is supported for backwards compatibility, but this use is deprecated and will be removed in a future release.)

  • If a literal value is provided, it may only contain A – Z or 0 – 9.

  • You cannot use COBOL reserved words, such as ZEROS or LOW-VALUES . If you want the filter to check for ZEROS , you need to use either the "?" placeholder or the physical numeric value "0000".

  • If multiple search conditions are provided, they must be connected with either an "AND" or an "OR" conjunction. (You can also place an AND or OR at the beginning of the FILTER-STRING without causing an error, but it will be ignored and replaced with an implicit AND.)

  • The total count of "(" and ")" must balance.

  • If you include literal values in the filter string, the value's format must exactly match the PIC format of the working storage of the field that you are comparing to. Thus, you might need to space-pad alpha field values or zero-pad numeric field values. For example, if you want to provide a literal value of "AR" for the GLT-SYSTEM field and it is a PIC A(05) field, you would format the condition:

    "(GLT-SYSTEM = 'AR   ')"

  • You can provide more than one value for a single field. This can be done both with ranges of values "((ARO-DUE-DATE >= ?) AND (ARO-DUE-DATE <= ?))" and with lists of values "((GLT-STATUS = '1') OR (GLT-STATUS = '3'))". The filter string does not support comparisons to multiple values. That is, you cannot say "(GLT-STATUS = '1' OR '3')".

  • To specify an occurring field, use (number). You cannot use a working storage field to specify the value. The literal value must be supplied. That is, "(GAM-DB-AMOUNT(I2) >= ?)" is not valid because the filter APIs have no idea what the value of I2 is, but "(GAM-DB-AMOUNT(10) >= ?)" is valid and will compare the supplied value against the 10th occurrence of DB-AMOUNT.

FILTER-STRING Content Example

(EMP-EMP-STATUS = 'FT')
AND (EMP-EMP-STATUS = 'FT')
((EMP-ZIP LIKE '55102%') and ((EMP-EMP-STATUS = 'FT') OR 
   (EMP-EMP-STATUS = 'PT')))
((ARO-TRANS-DATE >= ?) AND (ARO-TRANS-DATE <= ?))
AND (ARO-TRANS-DATE >= ?) AND (ARO-TRANS-DATE <= ?)

Additional Guidelines for Filters

  • You can only specify a database filter for database fields. You cannot specify a database filter for derived fields, string fields, and other non-database fields.

  • If you use both "AND" and "OR" conjunctions in a filter string, be sure to use parentheses to control precedence.

  • A filter can have no more than 32 separate search conditions.

  • The largest field that can be searched (in 9.0.x) is 127 bytes long. This allows all numeric fields to be searched (including BCD, YYYYMMDD, TIME, and so on), but may limit the searching of alpha fields in some cases.

  • The total size of fields that can be searched is less than the product of these two limits. That is, you cannot search 32 x 127 byte fields. All of the filter information in the C filter APIs (file handle, field numbers and occurrence numbers, filter description, filter values, and so on) must fit into a 4 KB memory region. If you create a filter that requires more than 4 KB of memory to describe, a run-time error will occur when you call the Index Filter function that uses the filter.

  • In addition to any groupings provided in the filter string, the Index Filter APIs will group all of the conditions in a single set of parentheses and the filter will be ANDed with the keys provided to the Index Filter API. So the entire set of search criteria is (keys) AND (filter). An AND or OR conjunction provided at the beginning of the filter string is ignored and replaced by this implicit AND. That is, there is no way to say (keys) OR (filter).

  • Filter values should be set in the same place that the filter string is created. This ensures that the logic used to set the values is exactly the same as the logic that was used to create the filter string.

  • For every "?" in FILTER-STRING , you must call one of the 890-SET-<Type>-FILTER-VALUE functions. The values must be set in the same order that they appear in FILTER-STRING .

  • The value of a parameter marker (the ? syntax) can be set before calling 890-CREATE-FILTER if you use the USEEARLYBINDFILTERS function. This function lets the run-time system know that the program will be setting values before the filter is created. This setting remains in effect until you call 890-CREATE-FILTER. 890-CREATE-FILTER changes this setting back to the default behavior.

  • Calling 890-CREATE-FILTER with spaces in FILTER-STRING creates a filter that has no conditions and that can be used with the Index Filter APIs.

Sample Filter

The following code shows a filter that allows a user to enter "to" and "from" values for ARO-DUE-DATE. Because the filter values are set before the call to 890-CREATE-FILTER, the USEEARLYBINDFILTERS function is used.

CALL "USEEARLYBINDFILTERS" USING WS-TRUE.
IF (AR90F1-SEL-DUE-DATE-FROM    > ZEROS)
    STRING "(ARO-DUE-DATE >= ?)" DELIMITED BY SIZE
           INTO FILTER-STRING
           POINTER AR90WS-FILTER-LENGTH
    MOVE AR90F1-SEL-DUE-DATE-FROM    TO DATETIME-FILTER-VALUE
    PERFORM 890-SET-DATETIME-FILTER-VALUE.
IF (AR90F1-SEL-DUE-DATE-TO      > ZEROS)
    STRING " AND (ARO-DUE-DATE <= ?)" DELIMITED BY SIZE
           INTO FILTER-STRING
           POINTER AR90WS-FILTER-LENGTH
    MOVE AR90F1-SEL-DUE-DATE-TO      TO DATETIME-FILTER-VALUE
    PERFORM 890-SET-DATETIME-FILTER-VALUE.
PERFORM 890-CREATE-FILTER.
PERFORM 850-FILTERNLT-AROSET5.