IF Versus WHERE

You may have noticed some report requests use IF screening statements and others use WHERE screening statements. Some even use both types of screening statements in the same report request! So you might be asking yourself, what is the difference between IF and WHERE?

The difference is that WHERE has more functionality. WHERE can do everything IF can do, and more. The only advantage of using IF is that alphanumeric and smart date values do not have to be enclosed in single quotes. The advantages of using the WHERE phrase are:

You can write screening statements with AND or OR conditions and add parentheses to explicitly specify the order of evaluation.

You can perform field-to-field comparisons and include arithmetic operators (+, -, *, / ,**).

You can select records based on the aggregate (SUM, COUNT) value of a field by using the WHERE TOTAL phrase.

Oftentimes you don't need the extra functionality of WHERE for your report requests, so it's perfectly acceptable to use IF for simple screening statements and use WHERE for complex screening statements. Both phrases can be used in the same report request as long as the correct syntax is used for each command.

For more information on selecting records, refer to your FOCUS documentation.

Related Topic: WHERE Screening Statements