Relational operators in DataWindow expressions
You use relational operators to compare a value with other
values. The result is a boolean expression whose value is always true or false.
Since the result of a boolean expression is always true or false,
a relational operator that compares a value to null evaluates
to false. For example, the expression “column > 5” evaluates
to false (and “NOT column > 5” evaluates to true)
when the column value is null.
When you write an expression, you can use the following relational
operators (more information about LIKE, IN,
and BETWEEN follows the table):
|
Operator |
Meaning |
Example |
|---|---|---|
|
= |
Is equal to |
Price = 100 |
|
> |
Is greater than |
Price > 100 |
|
< |
Is less than |
Price < 100 |
|
<> |
Is not equal to |
Price <> 100 |
|
>= |
Greater than or equal to |
Price >= 100 |
|
<= |
Less than or equal to |
Price <= 100 |
|
NOT = |
Is not equal to |
Price NOT= 100 |
|
LIKE |
Matches this specified pattern. |
Emp_lname LIKE |
|
IN |
Is in this set of values. |
Dept_id IN (100, 200, 500) |
|
BETWEEN |
Is within this range of values. The range |
Price BETWEEN 1000 AND 3000 |
|
NOT LIKE |
Does not match this specified pattern. |
Emp_lname NOT LIKE |
|
NOT IN |
Is not in this set of values. |
Dept_id NOT IN (100, 200, 500) |
|
NOT BETWEEN |
Is outside this range of values. The |
Price NOT BETWEEN 1000 AND 2000 |
Special characters for operations with strings
You can use the following special characters with relational
operators that take string values:
|
Special character |
Meaning |
Example |
|---|---|---|
|
% (percent) |
Matches any group of characters. |
Good% matches all names that begin |
|
_ (underscore) |
Matches any single character. |
Good _ _ _ matches |
LIKE and NOT LIKE operators
Use LIKE to search for strings that match a predetermined
pattern. Use NOT LIKE to search for strings that do not match a
predetermined pattern. When you use LIKE or NOT LIKE, you can use
the % or _ characters
to match unknown characters in a pattern.
For example, the following expression for the Background.Color
property of the Salary column displays salaries in red for employees
with last names beginning with F and displays all other salaries
in white:
|
1 |
If(emp_lname LIKE'F%',RGB(255,0,0),RGB(255,255,255)) |
Escape keyword
If you need to use the % or _ characters
as part of the string, you can use the escape keyword
to indicate that the character is part of the string. For example, the _ character
in the following filter string is part of the string to be searched for,
but is treated as a wildcard:
|
1 |
comment LIKE ~'%o_a15progress%~' |
The escape keyword designates any character
as an escape character (do not use a character that is part of the
string you want to match). In the following example, the asterisk
(*) character is inserted before the _ character
and designated as an escape character, so that the _ character
is treated as part of the string to be matched:
|
1 |
comment like ~'%o*_a15progress%~' escape ~'*~' |
BETWEEN and NOT BETWEEN operators
Use BETWEEN to check if a value is within a range of values.
Use NOT BETWEEN to check if a value is not in
a range of values. The range of values includes the boundary values
that specify the range.
For example, the following expression for the Background.Color
property of the Salary column displays salaries in red when an employee’s
salary is between $50,000 and $100,000 and displays
all other salaries in white:
|
1 |
If(salary BETWEEN 50000 AND 100000, RGB(255,0,0), RGB(255,255,255)) |
You can use the BETWEEN and NOT BETWEEN operators with string values.
For example, if the following expression is used for the Visual
property of a column, column values display only for departments
listed alphabetically between Finance and Sales:
|
1 |
If(dept_name BETWEEN 'Finance' AND 'Sales',1,0) |
The % or _ characters
can be used when you are using string values with the BETWEEN and
NOT BETWEEN operators. This example might include more department
listings than the previous example:
|
1 |
If(dept_name BETWEEN 'F%' AND 'S%',1,0) |
You can also use the BETWEEN and NOT BETWEEN operators with methods.
For example:
|
1 |
GetRow( ) BETWEEN 5 AND 8 |
IN and NOT IN operators
Use IN to check if a value is in a set of values. Use NOT
IN to check if a value is not in a set of values.
For example, the following expression for the Background.Color
property of the Salary column displays salaries in red for employees
in department 300 or 400 having a salary between $50,000
and $100,000, and displays all other salaries in white:
|
1 |
If(dept_id IN (300,400) and salary BETWEEN 50000 AND 100000, RGB(255,0,0), RGB(255,255,255)) |