Filtering Data with the SELECT Parameter

Use the punctuation shown in the following table to filter the data returned by the SELECT parameter in a Data Service call. Consider the following rules when creating a SELECT filter:

  • The ^ (caret) character preceding the SELECT operator indicates the comparison is to be case-insensitive based.

  • If spaces exist in value strings, replace them with + (plus) signs. If the value is all spaces, use the word spaces.

  • Wild cards can also be used in filtering expressions. The ^ (caret) character still applies.

  • Expressions without parentheses are evaluated based on the precedence of the conjunctions (& and |) similar to how the Java programming language evaluates Boolean expressions.

    Note: For upgrading customers, this is a difference between DME (8.0 version) and the Data service. DME read expressions from left to right and did not evaluate precedence and conjunctions.

    For example, because the | (OR) conjunction has a lower precedence than the & (AND) conjunction, the following filter syntax statements return the same data records.

    CITY=Akron|CITY=Boston&ZIP=12345
    CITY=Akron | (CITY=Boston&ZIP=12345)
  • Queries that specify a related field in the SELECT expression perform worse than queries that do not. SELECT expressions that do not have related fields can be done by the database layer, while those with related fields must be done in the IOS layer.

    Consider the following restrictions when using relations in filters:

    • If there are multiple references to relations in a filter, all relations referenced must be One-To-One relation types.

    • If a One-To-Many relation is referenced in a filter, it must be the single and only relation reference of any kind in the filter.

    • Multiple references to the same One-To-Many relation are not allowed.

    • Mixing One-To-One and One-To-Many relations is not allowed in a query.

  • Date fields in SELECT filter may have a value of SYSDATE , in place of an actual date string, to perform a query against the current system date of the LSF server. In addition to this, SYSDATE can be appended with an offset value of (+/-) a number of days.

    For example, to get all records created 30 days ago, you would use the syntax:

    SELECT=DATE-FIELD=SYSDATE-30

    - or -

    If the table has an END-DATE field, to get all records with an END-DATE of tomorrow, you would use the following syntax with the '+' escaped:

    SELECT=END-DATE=SYSDATE%2B1
  • Because the browser treats the following characters as special symbols, they must be replaced with their hex equivalent strings:

    Character Hex equivalent
    & (ampersand) %26
    < (less than) %3C
    > (greater than) %3E
    / (forward slash) %2F
    + (plus) %2B

    For example, to select fields with the text and/or, you would use the syntax:

    SELECT=FIELD=and%2For

The following table lists valid syntax for the SELECT parameter.

Punctuation Description
= (equals) Specifies field equals value.
!= Specifies field does not equal value.
< Specifies field is less than value.
> Specifies field is greater than value.
<= Specifies field is less than or equal to the value.
>= Specifies field is greater than or equal to the value.
~ Specifies field contains the value.
!~ Specifies field does not contain the value.