Expressions

Logic Expressions

Logic expressions can be used in set, if and return. Entity references cannot be used in logic expressions. The reference record must be first fetched from the database into another variable.

Where Expressions

Where expressions can be used in fetch, for, count and entity sets offlinefilter. Entity references can be used in where expressions to access related entity data.

Be careful to always use the correct types when doing comparisons. If comparing a Text column to a Number the database is likely to return no results. When SQLite converts the Number to a Text value it will always add a decimal place.

For example: We have an Employees entity set that has an EmpNo with type Text:

variable EmpNoToFind Number;
variable FetchedEmployee Structure(Employee);

set EmpNoToFind = 507;
fetch Employees a where [a.EmpNo = EmpNoToFind] into FetchedEmployee; 

In the above example FetchedEmployee will be null even if there is an employee with an EmpNo of "507". EmpNo is a Text column and EmpNoToFind is a Number variable. When running the query SQLite converts the 507 number to the string "507.0". Since the string "507" != "507.0" no result is returned. The following would succeed:

variable EmpNoToFind Text;
variable FetchedEmployee Structure(Employee);

set EmpNoToFind = "507";
fetch Employees a where [a.EmpNo = EmpNoToFind] into FetchedEmployee;

Supported Operators

==
!=
!
or 
and

>
>=
<
<=

+
-
/
*
%

in (e.g. ProjectObjstate in ('STARTED','APPROVED'))