Category: Conditional Expressions

About Conditional Expressions

Conditional expressions use the conditional operators to compare operands (typically, value expressions). When evaluated, conditional expressions yield a logical result (true or false), depending if  the condition is satisfied.

Conditional Operators

Operator

Condition Being Evaluated

expr = expr

The values are equal.

expr <> expr

The values are not equal.

expr < expr

The left-hand value is less than the right-hand value.

expr <= expr

The left-hand value is less than or equal to the right-hand value.

expr > expr

The left-hand value is greater than the right-hand value.

expr >= expr

The left-hand value is greater than or equal to the right-hand value.

expr between expr and expr

The value before BETWEEN is greater than or equal to the value to the right of BETWEEN and less than or equal to the value to the right of AND.

expr not between expr and expr

The value before NOT BETWEEN is less than the value to the right of NOT BETWEEN and greater than the value to the right of AND.

expr in («expr»)

The value before IN is a member of the list of values to the right of IN.

expr not in («expr»)

The value before NOT IN is not a member of the list of values to the right of NOT IN.

expr is [$]null

The value on the left is $Null.

expr is not [$]null

The value on the left is not $Null.

expr like pattern

The value before LIKE matches the pattern specified to the right of LIKE.

expr not like pattern

The value before NOT LIKE matches the pattern specified to the right of NOT LIKE.

Note: The AND and BETWEEN expressions are not the Boolean AND.

Examples of Conditional Expressions

EmpNum > 1254

Logically true when EmpNum is 1254.

DepNum >= EmpNum

Logically true when DepNum is greater than or equal to EmpNum

FirstName = ‘Smith’

Logically true when FirstName is Smith.

DeptDesc <> ‘Sports’

Logically true when DeptDesc is anything but Sports.

EmpNum between 1000 and 2000

Logically true when EmpNum is in the range 1000-2000.

LastName like ‘% ith$’

Logically true when LastName matches the given pattern.

Event.EventName in (‘F1′,’F2′,’Escape’)

Logically true when EventName is in the list.

ProdCode not between 542 and 863

Logically true when ProdCode is outside the range 542-863.

LastName not in (‘Smith’,’Jones’)

Logically true when LastName is not in the list.

FirstName not like ‘ Sm%’

Logically true when Firstname fails to match the pattern.

Simple conditional expression can be combined into more complex Boolean expressions by using Boolean operators. Also, the Boolean NOT can be used to achieve the same result as the NOT version of BETWEEN, LIKE, and IN.

 

See Also

About Boolean Expressions

Pattern Matching

The conditional operators LIKE and = (equals) can be used to construct expressions in which the condition involves matching a value to a pattern rather than to another explicit value.

The patterns are usually quoted character strings constructed using special wildcard characters in addition to literal characters.

LIKE Patterns

Patterns for use with the LIKE operator can use the following wildcard characters:

  • the underscore (_) matches any single character

  • the percent sign (%) matches zero or any number of characters

For example, the pattern

‘_ ob%’

matches any character string whose second and third letters are ob, followed by zero or more characters. Robert, Bob, and Robin (among others) would match this pattern. Therefore, the expression

FirstName like ‘_ ob%’

is logically true if the value of FirstName is Robert, Bob, Robin, or any other string that matches the given pattern.

If you need to use a percent sign or underscore as a literal character in the pattern, you must “escape” the wildcard character by inserting a backslash ( ) preceding the wildcard.  You must also escape the backslash (two backslashes in total) as shown in the following example:

Grad like ‘100\%’

Very complex expressions can be used as patterns in a LIKE comparison. For example, if SuffixVar is a variable whose value is %, then the expression

FirstName like $ concat(‘S_e%p, SuffixVar)

is logically true if FirstName matches the pattern S_E%p%.

= (Equals) Patterns

Patterns for use with the = (equals) operator also support a wildcard character for pattern matching. The question mark (?) matches any number of characters at the end of a string. The following example

DeptNum= ‘D56’?

is logically true if DeptNum begins with the characters D56. Notice that the question mark must appear outside the pattern string.

If you wish to use the question mark as a literal character, it must be included in the pattern string (i.e., within the quotation marks). The following example

RemarkField = ‘Smith?’

is logically true only if the value of RemarkField is exactly Smith?.

The question mark wildcard can be appended to any atomic expression (i.e., literals, object names, and temporary names) provided that the expression has a character data type (i.e., ALPHA, CHAR, VARALPHA, and VARCHAR).

For example, suppose that LastName is a field in the fCustomer form, having the value Sm//. The expression

Surname = fCustomer.LastName?

is logically true if SurName begins with Sm. The trailing blanks in the pattern are ignored when the question mark is applied to a pattern that is not a literal.

If LastName has the value Sm?//, then the expression

Surname = fCustomer.Lastname

is logically true only if Surname is exactly Sm?.

Using the question mark wildcard in an = (equals) expression is equivalent to using a trailing percent sign wildcard in a LIKE expression. For example, the following expressions are equivalent:

FirstName = ‘ Sm’?

FirstName like ‘ Sm%’

 

en_CAEnglish
pt_BRPortuguese en_CAEnglish