Screening Numeric Data
|
Topics: |
Fields can have either alphanumeric or numeric formats. Using the relational operators, along with WHERE, you can screen data of both types in the same request.
Start with a request that has no screening conditions. To display each employee salary in the EMPLOYEE data source, issue the following request:
TABLE FILE EMPLOYEE PRINT CURR_SAL BY LAST_NAME BY FIRST_NAME END
Run the request. The output is:
The salary for every employee is retrieved.
Suppose now that you want to see only employees whose salaries fall within a range. To retrieve employees whose salaries satisfy this condition, add a WHERE condition to the previous request.
Issue the following request:
TABLE FILE EMPLOYEE PRINT CURR_SAL BY LAST_NAME BY FIRST_NAME WHERE CURR_SAL GT 11000 AND CURR_SAL LT 20000; END
Notice that you left out the dollar signs ($) and commas (,) in each salary figure in the request. These are format edit options, which are not stored in the data source. Therefore, do not include either in your requests.
Run the request. The output is:
This time four (4) records are retrieved, each of which meets the screening conditions you included in the request. Notice that Alfred Stevens does not appear in this report since his current salary is $11,000.
Now, suppose you want to display employees earning less than a minimum or more than a maximum amount. In our last example, you saw how the AND relationship worked. In this next example, you explicitly change the AND relationship using the OR logical operator. As a result, any record that satisfies one OR another of the conditions is retrieved.
Issue the following request:
TABLE FILE EMPLOYEE PRINT CURR_SAL BY LAST_NAME BY FIRST_NAME WHERE CURR_SAL LT 11000 OR CURR_SAL GT 20000; END
Run the request. The output is:
Notice that there are some salaries in this report that meet the first condition (LT 11000) and others that meet the second condition (GT 20000). If you used the AND logical operator, your report would contain no data because no salary record could be less than $11,000 and greater than $20,000 at the same time.
You do not always use a field as a basis of comparison. Rather, you can use a calculation using WHERE. For instance, a company is giving employees a 10 percent increase. You want to find out which employees, even after the increase, are still making less than $10,000 a year. The field you use is CURR_SAL. The calculation is CURR_SAL increased by 10 percent (CURR_SAL * 1.1). The parentheses are optional, but are used for clarity.
Issue the following request:
TABLE FILE EMPLOYEE PRINT LAST_NAME FIRST_NAME HIRE_DATE CURR_SAL WHERE (CURR_SAL * 1.1) LT 10000; END
Run the request. The output is:
You see from this report that only Mary Greenspan is earning less than $10,000.
Defining Ranges
You have actually already included a range in a report request by creating the report that displayed four (4) records for salaries greater than $11,000 and less than $20,000. A range sets a lower and upper limit. Here is another way to specify a range. Notice that using a range is another way to specify the AND relationship.
Issue the following request:
TABLE FILE EMPLOYEE PRINT CURR_SAL BY LAST_NAME WHERE CURR_SAL FROM 11000 TO 20000; END
Run the request. The output is:
There is a subtle difference between using FROM ... TO to specify a range and using GT and LT. To see the difference, compare this report to the earlier one that displays salaries greater than $11,000 and less than $20,000.
This report displays an employee named Stevens who earns $11,000. In the previous report, Stevens is omitted. This is because the GT and LT operators exclude the test values, while the FROM ... TO operator includes them.
Remember, the test values are those values to which each record is compared.
In this scenario, to exclude the test values of 11000 and 20000 from your report, issue the following request:
TABLE FILE EMPLOYEE PRINT CURR_SAL BY LAST_NAME WHERE CURR_SAL FROM 11000.01 TO 19999.99; END
Run the request. The output is:
Combining Ranges and WHERE Conditions
You can combine range limitations with other WHERE conditions. Suppose you are interested in retrieving information about employees with a certain salary level and a certain number of training hours. To retrieve this information, issue the following request:
TABLE FILE EMPLOYEE PRINT FIRST_NAME LAST_NAME CURR_SAL ED_HRS BY LAST_NAME NOPRINT WHERE CURR_SAL GT 15000 AND ED_HRS FROM 10 TO 50; END
Run the request. The output is:
Here is another example using WHERE to combine more than one condition to narrow down records. In many companies, employees must be with the company for a specified length of time, or attain a certain title to qualify for additional vacation time. In the following example, you retrieve employees who satisfy at least one of these two requirements, that they started work before 1981, or that their job code (A15, A16, or A17) indicates a managerial position.
Issue the following request:
TABLE FILE EMPLOYEE PRINT LAST_NAME FIRST_NAME HIRE_DATE CURR_JOBCODE WHERE HIRE_DATE LT '810101' OR CURR_JOBCODE EQ 'A15' OR 'A16' OR 'A17'; END
Run the request. The output is:
Screening Aggregated Values
So far you have used WHERE to screen individual values. Before any value is retrieved, it is evaluated to make sure it satisfies the condition or conditions in the WHERE statement. But, you may recall from Chapter 1, that the SUM and COUNT verb commands produce summary information rather than individual values. This process is called aggregation and requires use of the WHERE TOTAL condition.
The WHERE TOTAL condition screens aggregated values produced by SUM or COUNT. Instead of evaluating data before it is retrieved, WHERE TOTAL works on data after it has been retrieved and processed. Whenever you want to screen the results of an aggregation, you must use WHERE TOTAL.
To determine which job codes exceed a minimum training requirement of 35 hours, issue the following request:
TABLE FILE EMPLOYEE SUM ED_HRS BY CURR_JOBCODE WHERE TOTAL ED_HRS GT 35; END
Run the request. The output is:
| WebFOCUS | |
|
Feedback |