Restriction Clauses

Restriction Clauses

A restriction clause is an ASCII text string of operators and expressions. A restriction clause specifies selection criteria for the values in the columns of a view, limiting the number of rows the view contains. The syntax of certain clauses (such as WHERE or HAVING) requires using a restriction clause. A restriction clause can specify these conditions:

•

Restriction condition — Compares an expression that references a column value to either a constant or another expression that references a column value in the same table.

•

Join condition — Compares an expression that references a column value from one table to an expression that references a column value from another table.

A restriction clause can contain multiple conditions. It can also contain a SELECT subquery that bases search criteria on the contents of other tables in the database. The condition containing the subquery can contain the EXISTS, NOT EXISTS, ALL, ANY, and SOME keywords, or the IN range operator.

You can specify a restriction clause using a WHERE or HAVING clause in a SELECT, UPDATE, or DELETE statement.

Figure 1 provides an example restriction clause and illustrates restriction clause elements.

Figure 1 Example Restriction Clause

Restriction Clause Operators

Restriction clauses can use three types of operators:

•

Boolean operators — Connect conditions in a restriction clause.

•

Condition operators — Connect expressions to form a condition. A condition operator can be a relational or a range operator.

•

Expression operators — Connect two expressions to form another expression. An expression operator can be an arithmetic or string operator.

Boolean Operators

Boolean operators specify logical conditions.

Table 43

Boolean Operators

If all search conditions connected with AND are true, the restriction passes.

If at least one of the conditions connected by OR is true, the restriction passes.

If the condition is false, the restriction passes.

Condition Operators

A condition operator can be a relational or a range operator.

•

Relational operator—Compares a column value with either another column value or a constant. If the value of the column is true, Pervasive PSQL selects the row.

•

Range operator—Compares a column value with a specified range of values for the column. If the value of the column is true, the restriction passes, and Pervasive PSQL selects the row.

Table 44 lists the relational operators.

Table 44

Relational Condition Operators

Greater than or equal to

Less than or equal to

Table 45 lists the condition operators.

Table 45

Range Condition Operators

Value exists in specified list.

Value does not exist in specified list.

Value exists within specified range.

Value does not exist within specified range.

Value is the defined NULL value for the column.

Value is not the defined NULL value for the column.

Value matches specified string. You can substitute two wildcard characters for actual characters. The percent sign (%) represents any sequence of n characters (where n can be zero). The underscore ( _ ) represents a single character.

Value does not match specified string.

With the IN and NOT IN operators, the second expression can be a subquery instead of a column name or constant.

Expression Operators

Expression operators allow you to create expressions for computed columns using arithmetic or string operators. For more information, refer to Functions.

Restriction Clause Examples

The following examples demonstrate some of the restriction clause operators.

OR and Equal To (=)

The following example uses the relational EQUAL TO and boolean OR operators. It selects all rows in which the value of the State column is Texas or New Mexico.

SELECT Last_Name, First_Name, State
FROM Person
WHERE State = 'TX' OR State = 'NM'#

The following example uses the IN operator. It selects the records from the Person table where the first names are Bill and Roosevelt.

SELECT * FROM Person WHERE First_name IN

(’Roosevelt’, ’Bill’)#

The following example uses the LIKE operator:

SELECT ID, First_Name, Last_Name, Zip
FROM Person
WHERE Zip LIKE '787%';

This example retrieves records in the Person table where the zip code begins with ‘787’.