Complex expressions

Nested qualification descriptors

WHERE year > 95 AND (quarter = 1 OR quarter = 3)

The OR operator combines two functions, equal(quarter,1) and equal(quarter,3). If either is true, the combination is true. The AND operator combines the result of the greaterthan(year,95) with the result of the Boolean OR operator.

If a WHERE clause contains multiple conditions, the database server constructs a qualification descriptor that contains multiple, nested qualification descriptors.

WHERE region = "southwest" AND 
   (balance < 90 OR aged <= 30)

The nested qualification descriptors for the preceding WHERE clause have a hierarchical relationship, as the following figure shows.

Figure 1. Hierarchy of nested qualification descriptors

The WHERE clause is processed in the following order:

1. The Q1 qualification descriptor handles the lessthan(balance,90) function.
2. The Q2 qualification descriptor handles the lessthanequal(aged,30) function.
3. The Q3 qualification descriptor handles the OR operation on the results of the Q1 and Q2 descriptors.
4. The Q4 qualification descriptor handles the equal(region,’southwest’) function.
5. The Q5 qualification descriptor handles the AND operation on the results of the Q4 and Q3 descriptors.

Parameter descriptors and qualification descriptors

If a WHERE clause has multiple expressions, columns, or constants, use parameter descriptors to describe all the parameters in the qualifications. A parameter descriptor is also necessary if the qualification consists of a function that takes a single value. Qualification descriptors and parameter descriptors can be nested to reflect the qualification hierarchy. Nested qualification and parameter descriptors are processed recursively.

The following example has multiple levels of qualifications:

WHERE udr1(a,7,4)>5 AND b=2 AND udr2(c)

The following example shows how the functions from the previous example are nested:

AND (greaterthan(udr1(a,7,4),5), AND (equals(b,2), udr2(c)))

The nested qualification descriptors and parameter descriptors for the preceding WHERE clause have a hierarchical relationship, as the following figure shows.

Figure 2. Hierarchy of nested qualification and parameter descriptors

The WHERE clause is processed in the following order:

1. The Q1 qualification descriptor starts evaluating the greaterthan function, whose parameters are described in the P1 parameter descriptor. The P1 parameter descriptor contains two parameters: an expression, which is contained in the Q2 qualification descriptor, and the constant 5.
2. The Q2 qualification descriptor evaluates the expression udr1, whose parameters are described in the P2 parameter descriptor. The P2 parameter descriptor contains three parameters: the column a, the constant 7, and the constant 4.
3. The Q1 qualification descriptor uses the result of the Q2 qualification descriptor to complete the evaluation of the greaterthan function.
4. The Q3 qualification descriptor evaluates the equals function, whose parameters are described in the P3 parameter descriptor. The P3 parameter descriptor contains two parameters: a column b and the constant 2.
5. The Q4 qualification descriptor evaluates the expression udr2, whose parameters are describes in the P4 parameter descriptor. The P4 parameter descriptor contains one parameter: the column c.
6. The Q5 qualification descriptor evaluates the AND operator on the results of the Q1, Q3, and Q4 qualification descriptors.