Where Clause
The where clause tells the database what rows you want to retrieve. The value of searchcondition is a logical (Boolean) database expression that defines some criteria for row selection
When you include this clause, the select statement returns only those rows that fit the criteria defined in the search condition. If you do not include a where clause, the select statement returns as many rows as there are rows in the table. (If the select statement unions two or more selects, then it returns all the rows from the table in each select. Similarly, if a select joins two or more tables, then it returns a Cartesian product if there is no where clause.) The where clause lets you specify which rows you want returned by the select statement.
The syntax of the where clause is:
where searchcondition
searchcondition
Specifies a logical (Boolean) expression. The expression must compare a column value (or an aggregate of column values) to some expression, such as a constant value or a field in the current form.
For example:
select name as name, jobtitle as jobtitle
from employee
where salary >= :sallimit;
This statement selects into the frame variables the name and job title of the first employee returned whose salary is equal to or greater than the value entered in the sallimit field.
You can use simple variables in place of constants in the search condition. For example:
where dept = :var and name = 'Jones' and
age > :age
You can also place the entire search condition in a varchar variable. For example:
qual = 'dept = ''mktg'' and age > 10 and name =
''Jones''';
...
select...
from...
where :qual;
To build up a search condition from the contents of existing varchar and numeric variables, you could do something like this:
qual = 'dept = ''mktg'' and age > ' + varchar(age) + ' and name =
''' + ename + '''';
If you place the entire search condition in a varchar variable, you cannot use the repeated keyword in the select statement.