Filter String Formatting Rules and Examples

E@FLTSTRING 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 RPG reserved words, such as *ZEROS or *LOVAL. 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 E@FLTSTRING 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 5-character 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.

E@FLTSTRING Content Example

CALLP     U@STR@INIT(%ADDR(E@STR@DS):
          %ADDR(E@FLTSTRING): 1:
          %SIZE(E@FLTSTRING): 1:)
CALLP     U@STR@ITEMSZ(%ADDR(E@STR@DS):
          '((RLN-REQ-NUMBER="?")': 1: 21)
CALLP     U@STR@ITEMSZ(%ADDR(E@STR@DS):
          'AND': 1: 3)
CALLP     U@STR@ITEMSZ(%ADDR(E@STR@DS):
          '((RLN-STATUS=<="?"))': 1: 17)

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 E@FLTSTRING, you must call one of the U@FLTS<Type> functions. The values must be set in the same order that they appear in E@FLTSTRING.

  • Calling U@FLTCREATE with spaces in E@FLTSTRING 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 sets the filter string, creates the filter, and then sets the to and from values for transaction dates.

      EVAL      E@FLTSTRING    = '   (((ICT-TRANS-DATE<=- 
                "?") AND (ICT-TRANS-DATE>="?"))         - 
                              AND ((ICT-DOC-TYPE = "- 
                IS")                             OR (ICT- 
                -DOC-TYPE = "RR"))                      - 
                    AND (ICT-STATUS > "?"))'              
      CALLP     U@FLTCREATE                               
      EVAL      E@FLTVDATETIME = PWSTO#WRDTN              
      CALLP     U@FLTSDATETIME                            
      EVAL      E@FLTVDATETIME = PWSFROM#WRDTN            
      CALLP     U@FLTSDATETIME                            
      EVAL      E@FLTVNUMERIC  = *ZEROS          
      CALLP     U@FLTSNUMERIC                    
      EVAL      D@CMPNY        = F1@CMPNY        
      EVAL      E@DBBEGRNG     = 1               
      CALLP     DN@DBICICT1(E@DBFILTERBEGR)