Logic expressions can be used in
return. Entity references cannot be used in logic expressions. The reference record must be first fetched from the database into another variable.
Where expressions can be used in
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
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;
== != ! or and >= < <= + - / * % in (e.g. ProjectObjstate in ('STARTED','APPROVED'))