Filters 101 - Part 5
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
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).
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!
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