Operators used in DataWindow expressions

An operator is a symbol or word in an expression that performs an arithmetic calculation or logical operation; compares numbers, text, or values; or manipulates text strings.

Four types of operators are available:

Arithmetic operators in DataWindow expressions

When you write an expression, you can use the following arithmetic operators:

Operator

Meaning

Example

+

Addition

SubTotal + Tax

-

Subtraction

Price - Discount

*

Multiplication

Quantity * Price

/

Division

Discount / Price

^

Exponentiation

Rating ^ 2.5


Multiplication and division

Multiplication and division are carried out to full precision (16-18 digits). Values are rounded:

Expression

Value

20.0/3

6.666666666666667

3*(20.0/3)

20

Truncate(20.0/3,4)

6.6666


Calculations with null

When you form an arithmetic expression that contains a null value, the expression becomes null. Thinking of null as undefined makes this easier to understand. For example, when a null column is multiplied by 5, the entire expression also evaluates to null. Use the IsNull function to explicitly check for the null value.

Boolean expressions that contain a null value evaluate to false rather than to null. For more information, see Relational operators in DataWindow expressions.

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 'C%' OR Emp_lname LIKE 'G%'

IN

Is in this set of values.

Dept_id IN (100, 200, 500)

BETWEEN

Is within this range of values. The range includes the first and last values.

Price BETWEEN 1000 AND 3000

NOT LIKE

Does not match this specified pattern.

Emp_lname NOT LIKE 'C%' AND Emp_lname NOT LIKE 'G%'

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 range includes the first and last values.

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 with Good.

_ (underscore)

Matches any single character.

Good _ _ _ matches all 7-letter names that begin with Good.


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:

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:

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:

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:

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:

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:

If(dept_name BETWEEN 'F%' AND 'S%',1,0)

You can also use the BETWEEN and NOT BETWEEN operators with methods. For example:

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:

If(dept_id IN (300,400) and salary BETWEEN 50000 AND 100000, RGB(255,0,0), RGB(255,255,255))

Comparing strings in DataWindow expressions

When you compare strings, the comparison is case-sensitive. Leading blanks are significant, but trailing blanks are not.

Case-sensitivity examples

Assume City1 is "Austin" and City2 is "AUSTIN". Then:

City1=City2

returns false.

To compare strings regardless of case, use the Upper or Lower function. For example:

Upper(City1)=Upper(City2)

returns true.

For information about these functions, see Using DataWindow expression functions.

Blanks examples

Assume City1 is "Austin" and City2 is " Austin ". Then the expression:

City1=City2

returns false. PowerBuilder removes the trailing blank before making the comparison, but it does not remove the leading blank.

To prevent leading blanks from affecting a comparison, remove them with one of the trim functions: Trim or LeftTrim.

For example:

Trim(City1)=Trim(City2)

returns true.

To compare strings when trailing blanks are significant, use an expression such as the following to ensure that any trailing blanks are included in the comparison:

City1 + ">" = City2 + ">"

For information about these functions, see Using DataWindow expression functions.

Logical operators in DataWindow expressions

You use logical operators to combine boolean expressions into a larger boolean expression. The result is always true or false:

Operator

Meaning

Example

NOT

Logical negation.

If A is true, NOT A is false. If A is false, NOT A is true.

NOT Price = 100

AND

Logical and.

A AND B is true if both are true. A AND B is false if either is false.

Tax > 3 AND Ship < 5

OR

Logical or.

A OR B is true if either is true or both are true. A OR B is false only if both are false.

Tax > 3 OR Ship < 5


When you combine two or more boolean expressions to form a new expression, the new expression is either true or false. The following truth table shows how true and false expressions are evaluated to form an expression that is either true or false.

For example, if "My dog has fleas" is true and "My hair is brown" is false, then "My dog has fleas OR my hair is brown" is true, and "My dog has fleas AND my hair is brown" is false:

If one expression has this value

And the logical operator is

And if another expression has this value

The resulting expression has this value

TRUE

AND

TRUE

TRUE

TRUE

AND

FALSE

FALSE

FALSE

AND

TRUE

FALSE

FALSE

AND

FALSE

FALSE

TRUE

OR

TRUE

TRUE

TRUE

OR

FALSE

TRUE

FALSE

OR

TRUE

TRUE

FALSE

OR

FALSE

FALSE

NOT TRUE

AND

TRUE

FALSE

NOT TRUE

AND

FALSE

FALSE

NOT FALSE

AND

TRUE

TRUE

NOT FALSE

AND

FALSE

FALSE

NOT TRUE

OR

TRUE

TRUE

NOT TRUE

OR

FALSE

FALSE

NOT FALSE

OR

TRUE

TRUE

NOT FALSE

OR

FALSE

TRUE


If you use a logical operator with a boolean function that returns null, the term with the null return value is evaluated as false. If you use the NOT logical operator with a boolean function that returns null, the complete term evaluates to true. For example, NOT gf_boolean () evaluates to true when gf_boolean returns null.

Concatenation operator in DataWindow expressions

The concatenation operator joins the contents of two variables of the same type to form a longer value. You can concatenate strings and blobs.

To concatenate values, you use the plus sign (+) operator.

String expression

Value

"over" + "stock"

overstock

Lname + ', ' + Fname

If Lname is Hill and Fname is Craig, then "Hill, Craig"


Using quotes

You can use either single or double quotes in string expressions. For example, the expression "over" + "stock" is equivalent to the expression 'over' + 'stock'.