Was this helpful?
Comparison Operator Validation Checks
The format for a comparison operator validation check is a string containing one or more comparison operators. Comparison operator validation checks have the following conventional syntax:
fieldname comparisonoperator constant
fieldname comparisonoperator otherfieldname
fieldname IS NULL
fieldname IS NOT NULL
The fieldname is the internal name of the field being validated; otherfieldname is the internal name of some other field containing a value to be compared against. In a validation string for a table‑field column, you use tablefieldname instead of fieldname, as follows:
tablefieldname.internalcolumnname comparisonoperator constant
The following table lists valid comparison operators for SQL.
Operator
Description
=
Equal to
!=
Not equal to
<>
Not equal to
<
Less than
<=
Less than or equal to
>
Greater than
>=
Greater than or equal to
LIKE
SQL string equal to
Character String Comparisons
You can enter character strings as validation checks for fields designated as character data types. Always enclose character string validation checks in double quotation marks, except for the LIKE operator, which accepts strings with single quotation marks.
Validation checks on character strings can include pattern‑matching characters. The following pattern‑matching characters are valid for all operators except the LIKE operator:
Character
Description
*
(Asterisk) Matches zero or more undefined characters. For example, S* equals any character string beginning with the letter S.
?
(Question mark) Matches exactly one undefined character. For example, T?P equals TAP, TIP, TOP, etc.
[...]
Matches any characters between the brackets including ranges. For example, [ACL]* equals any string that begins with A, C, or L. ?[N‑X] equals any two-character string that begins with any letter and has any letter between N and X as the second character.
%
(Percent sign) Matches zero or more undefined characters (similar to the * character described above).
_
(Underscore) Matches one undefined character (similar to the ? character described above).
Example character string validations:
dept = "sales"
emptable.fname = "*son"
Date and Money Comparisons
Enter comparison constants for abstract data types such as date and money in double quotation marks, as if they were character strings. For example:
date = "1‑Jan‑1988"
projecttable.budget > "$1000000"
The money data type is not supported by OpenSQL. For SQL, OpenSQL and QUEL equivalents, see the appendix "Data Types."
NULL Value Comparisons
To compare for the null value, use IS NULL. To compare for any value but the null value, use IS NOT NULL. For example:
salary IS NOT NULL
The following rules govern null values in an expression:
A NULL value in an expression with relational or arithmetic operators causes the expression to evaluate to NULL.
If you use the logical AND operator and either expression is FALSE, then the result is FALSE. If one expression is TRUE and the other is NULL, then the result is NULL. The result is only TRUE if both expressions are TRUE.
If you use the logical OR operator and either expression is TRUE, then the result is TRUE. If one expression is FALSE and the other is NULL, the expression is NULL. The result is FALSE only if both expressions are FALSE.
If you use the logical NOT operator with an expression that evaluates to a null value, the result is still null.
Numeric Comparisons
Numeric constants do not have to be enclosed in quotes. The following example requires that the number entered in the Salary field be larger than zero:
salary > 0
This example requires that the number entered in the Age field be larger than or equal to 18 and less than or equal to 70:
age >= 18 AND age <= 70
A complex numeric expression must be enclosed in parentheses to clarify the way the expression must be evaluated.
The following example requires that the number entered in the Hourly_rate field either equal 30 or fall within the range of 40 to 50:
hourly_rate = 30 OR (hourly_rate >= 40 AND
hourly_rate <= 50)
Comparisons Against Other Fields
You can compare a value against whatever value is currently contained in another field. The following example requires that the name entered in the Lastname field not be the same as the name entered in the Firstname field:
lastname!= firstname
This example requires that the date entered in the Duedate field be equal to or greater than the date entered in the Orderdate field:
duedate => orderdate
Comparison to a List of Values
By using the keyword in, you can compare a value against an arbitrary list of values using the following format:
fieldname in [list]
The fieldname parameter is the internal name of the field and list is the list of valid values. The values in list must be of the same data type as values admissible in the field. They must be separated by commas and enclosed in brackets ([ ]).
If the field has a character data type, you must enclose the character string values in double quotation marks and you can use valid pattern‑matching characters. Leading blanks are not significant.
The following example requires that the name entered in the Lname field be either Jones, or Ortega, or Bridges:
lname in ["Jones", "Ortega", "Bridges"]
This example requires that the character string entered in the Address field contain the characters Bl, or Av, or end in R plus one other letter, or end with St:
address in ["*Bl*", "*Av*", "*R?", "*St"]
This example requires that the amount entered in the Salary column of the Emptable table field be either 1000, 1100, 1200, 1300, 1400, or 1500:
emptable.salary in [1000, 1100, 1200, 1300, 1400, 1500]
Comparison to a Lookup Table
The keyword in can also be used to compare data entered in a field to a set of stored values in a database table. This is the easiest way to compare against a large number of values or against values that can be changed from time to time.
When your form is initialized in QBF or your own application, the current set of data from the lookup table is read into main memory. The column entries in the lookup table at the time of form initialization represent the data values used for comparison. Values added to, or deleted from, the lookup table column after the form is initialized are not reflected in the set of data used for comparison.
To compare against values in a lookup table, use the following format:
fieldname|tablefield.column in
   [schema.]tablename.columnname
If the schema, tablename, or columnname after the in keyword is a delimited identifier, you must enclose it within double quotes:
fieldname|tablefield.column in
   ["schema name".]"table name"."column name"
where:
fieldname
Identifies the internal name of the field being validated
tablefield.column
Identifies the internal name of the tablefield and column being validated
schema
Identifies the schema to which the table belongs and its implied owner
tablename
Identifies the name of a table in the current database
columnname
Identifies the name of a column in the specified lookup table
The following example requires that the value in the Zipprefix field match the values found in the Prefix column of the Zip table:
zipprefix in zip.prefix
This example requires that the names entered in the Manager column of the Emptable tablefield match names contained in the Name column of the Employee table:
emptable.manager in employee.name
The user can place the cursor in a field that is validated by a lookup table or by a fieldname in list validation check, as described in Comparison to a List of Values. In either case, choosing the Help Field operation at this point displays the acceptable values from the lookup table.
Boolean Operators in Validation Checks
This chapter has discussed the following validation comparison types:
Simple relational operator comparisons
Comparison to a list of values
Comparison to a lookup table
You can create more complex validation checks by using Boolean operators to connect any of these validating comparison types. The syntax for using the Boolean operators is:
expression OR expression
expression AND expression
NOT expression
The parameter expression is any of the previously discussed validation comparison types. For example, the following validation check forces a user to enter a number into the Code field that is either less than 21 or is 25, 30, or 35:
code < 21 or code in [25, 30, 35]
To allow additional flexibility, you can use parentheses to group Boolean expressions to achieve the desired semantics in the validation check. For instance, the following example forces a user to enter a number into the code field that is less than or equal to 20000 into the Salary field unless the Grade field is greater than or equal to 7. In this case, the user can enter a number up to 30000:
salary <= 20000 or (grade >= 7 and salary <= 30000)
Operator
Description
AND
Boolean conjunction
OR
Boolean disjunction
NOT
Boolean negation
Negation (NOT) has precedence over conjunction (AND) and disjunction (OR); AND and OR have equal precedence.
Some example character strings with Boolean operators are:
manager = "Jones" OR manager = "Ortega" AND NOT manager = "Fisher"
Last modified date: 08/28/2024