The Where Clause

The optional where clause qualifies a database retrieval according to the specified logical (Boolean) expressions. The where clause is a Boolean combination of conditions (a QUEL predicate or an invocation of the qualification function).

Use a simple logical expression that compares a column in a database table to an expression, such as a constant value or a field in the current form. For example:

empform := retrieve
(employee.name, employee.jobtitle)
where employee.salary >= sallimit;

This statement retrieves into Empform the names and job titles of all employees whose salaries are greater than the value in the Sallimit field.

Place the qualification function in a where clause to allow the end user to include comparison operators (such as != or >) in the search condition at run time. The application user can type values, comparison operators, or wildcard characters into the visible simple fields in the current form specified by the qualification function.

The II_PATTERN_MATCH logical sets pattern matching to SQL or QUEL. Unless II_PATTERN_MATCH is set differently, you must use the wildcard characters for the query language of the application.

In QUEL, use the question mark (?) and the asterisk (*) as wildcards. The characters are defined during installation. The question mark (?) represents a single character, and the asterisk (*) represents any number of characters. For a list of comparison operators, see the section, Boolean Comparison Operators in Logical Expressions.

The example below shows a query operation that utilizes the qualification function:

range of e is employee;
empform := retrieve (idnum = e.empnum,
  e.jobtitle, salary = e.empsal)
  where qualification (e.jobtitle = jobtitle,
    e.empsal = salary);

By entering the values "*Manager*" and "<30000" in the jobtitle and salary fields, respectively, the user retrieves information on all employees having a job title containing the word "Manager" and whose salaries are under $30,000.

The requirements for using the qualification function are:

• In the qualification function, only fields on a form are allowed. Local variables and hidden table-field columns are not allowed.

• Queries in which the qualification function is used are not suitable for use as repeated queries. The keyword repeat is ignored if you use it with such queries.