Where Expressions

Where expressions are used to select the desired records from a table when using the record or records function, the number of records function, or the delete command. A where expression includes one or more tests of the form columnName operator value.

The name of the column must come first, followed by the operator and then the value. For example, in the expression "where size is at least 12" the column name is "size" and must come before the operator. The operator "is at least" is one of the ways to test whether something is greater than or equal to another value, and 12 is the value that will be used in comparing to the value of the size column.

The operator can be any of the following (or any of their synonyms or negative variants):

  • is equal to
  • is not equal to
  • is less than
  • is more than
  • is less than or equal to
  • is greater than or equal to
  • begins with
  • ends with
  • contains <substring>
  • is in <list of values>
  • is between <min> and <max>

These operators can be used to test whether a column has a value assigned:

  • columnName is null
  • columnName is not null

In addition, multiple conditions can be tested at once by combining them with AND or OR. In a complex where expression it may be helpful to enclose subexpressions in parentheses in order to make the meaning clear. For example:

delete records from billingTable where (dueDate is earlier than today) and (remainingAmountDue is 0)

 

This topic was last updated on October 11, 2019, at 11:18:55 AM.

Eggplant icon Eggplantsoftware.com | Documentation Home | User Forums | Support | Copyright © 2019 Eggplant