Writing Expressions

Selection expressions are used when doing advanced row selection or advanced row find. Value expressions are used when creating computed columns. This section describes how to write such expressions.

Introduction and Examples

Selection expressions, which are similar to what some may call boolean expressions, can be used to select rows of a table. Examples of such expressions are:

  • price>24.99

  • (price+tax)>30

  • 200<((share/100)*total)

  • max(price1;price2)<100

  • (min(price1;price2)+(amount/100))<50

  • code=1

  • state='NY' 'CA'

Selection expressions must evaluate to 1 (i.e., true, include this row in the selection) or 0 (i.e., false, do not include this row).

Similar expressions are used when creating computed columns:  The computed column's value is given by a value expression. Value expressions can evaluate to any value so the examples above are valid for value expressions as well. Here are additional examples of value expressions:

  • price*1.01

  • price+tax

  • (share/100)*total

  • min(price1;price2)+(amount/100)

  • mod(min(col6;col2)+(col1/100);col3+2)

  • 12,345.67

  • 3/15/03

  • 13:45

  • 'my text'

The last four are simply a number, a date, a time, and a text value. In such cases, every row of the column has the same value.

Order of Operations

In an expression like:

a*b+c*d

it is important to know the order in which the operators are applied. Are the operators evaluated strictly in left-to-right order without regard to the type of operation? Is addition done first? Is multiplication done first? The answer is the latter, which is similar to standard mathematical notation and most spreadsheets and computer languages. The above expression is therefore equivalent to:

(a*b)+(c*d)

Here is a complete list of all the operators and the order in which they are applied:

  1. ^
  2. *,  /
  3. +,  -
  4. =,  <,  >,  <>,  <=,  >=
  5. &,  |
  6. ~

For example:

~a*b+c*d>e&x/y>z

is the same as:

~((((a*b)+(c*d))>e)&((x/y)>z))

When two operators have the same precedence, the one on the left is done first. For example:

a/b*c

is the same as:

(a/b)*c

which is not the same as:

a/(b*c)

It is therefore possible to write fairly complex expressions without parentheses, but bear in mind that parentheses can improve readability. 1010data recommends that you use parentheses even when they are not strictly necessary.