Filter data with the SELECT parameter

Use the punctuation shown in the table to filter the data returned by the SELECT parameter in a Data Service call. Consider these 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, these 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. On the other hand, those with related fields must be done in the IOS layer.

  Consider these 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 a 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 this syntax with the '+' escaped:

  SELECT=END-DATE=SYSDATE%2B1

• Because the browser treats these 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

This table lists valid syntax for the SELECT parameter.