As mentioned before, database filters can be very simple or complex; it depends on the situation. To accomodate this situation the database language has created what is called BOOLEAN OPERATORS. These are used to "join" together multiple database filter conditions (also known as Expressions). As you may have guessed, it is important to know how to use a BOOLEAN OPERATOR properly to achieve the desired result.

 

There are only a handful of BOOLEAN OPERATORS; you will use only 4 of these to create complex filter conditions. These are

 

 AND

 OR

 NOT

 XOR

 

Let's use some example situations and display what a sample filter for each would be:

 

Customers from Texas that make over $50000 a year

upper(CUS:State) = 'TX' AND CUS:Income > 49999

 

People who like ice cream or truffles

CUS:IceCream = 1 OR CUS:Truffles = 1

 

Cars that can be any color except orange

upper(INV:Color) NOT = 'BLACK'

 

Notice that every time we are comparing a text string (such as the state field) the function UPPER() is used. This is a good practice to get into; whenever your filters are using/comparing text use the UPPER() or LOWER() functions.

 

Notice that the CUS:IceCream and CUS:Truffle fields are true/false fields; a value of one means that the field is TRUE (zero would mean false).

 

Notice that the NOT operator is used with the equal sign; this BOOLEAN OPERATOR is most commonly used with another CONDITIONAL OPERATOR (greater than, less than, equal to).

 

It is important to understand that there is an order of precedence involved when combining two or more expressions into one filter. A simple definition for "precedence" can be "what the computer will look for first in the database". There are five levels of precedence:

 

Level 1: Parenthesis characters that surround a portion of a filter expression

Level 2: Conditional operators (<, >, =, etc)

Level 3: NOT

Level 4: AND

Level 5: OR

 

When the filter expression is evaluated the precedence order will determine the result of the filter. The filter always returns a value of either TRUE or FALSE; either the database field(s)  matches what you are checking for or it doesn't! There is no other result that a filter can return. Keeping this in mind here are some additional filter examples:

 

upper(CUS:Car) = 'FERRARI'

 

Above we are checking for customers who own cars made by Ferrari; if their car is a Honda then the filter condition returns a value of FALSE, and won't be selected for printing on the report.

 

Let's modify the filter to check for 'Ferrari' and 'Lotus' automobiles by using a BOOLEAN OPERATOR:

 

upper(CUS:Car) = 'FERRARI' AND upper(CUS:Car) = 'LOTUS'

 

In this example the BOOLEAN OPERATOR of AND is being used. However, let's examine what is happening, and why the above example will fail. The BOOLEAN OPERATOR of AND means that both expressions MUST return a value of TRUE for that database record to be selected. There is no way a car can be both a Ferrari AND an Lotus, so that database record will be skipped!

 

We need to change the BOOLEAN OPERATOR to the OR operator so the filter looks like this:

 

upper(CUS:Car) = 'FERRARI' OR upper(CUS:Car) = 'Lotus'

 

If the car isn't a Ferrari the OR BOOLEAN OPERATOR will continue evaluating the filter expression, since a value of TRUE isn't required for the first part of the expression. Therefore if the car is a Ferrari or Lotus that database record will be selected for printing.

 

You can create very long filters by using BOOLEAN OPERATORS; some examples:

 

Male customers over 30 who drive a Lincoln or Cadillac automobile

upper(CUS:Sex) = 'M' and CUS:Age > 30 AND upper(CUS:Car) = 'Lincoln' or upper(CUS:Car) = 'Cadillac'

 

Clients between the ages of 30 and 40

CUS:Age > 29 AND CUS:Age < 41