Kbase P117222: How to find the maximum value of a given integer or decimal field in a table?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
7/7/2006 |
|
Status: Unverified
GOAL:
How to find the maximum value of a given integer or decimal field in a table?
GOAL:
How to find the minimum value of an integer or decimal field in a table?
FIX:
There are three ways to obtain the maximum value of a decimal or integer field. Similar logic may be used to find the minimum:
1. Using the SQL-89 SELECT statement with the INTO option. For example:
DEFINE VARIABLE MaxBalanceValue AS DECIMAL NO-UNDO.
SELECT MAX(balance) INTO MaxBalanceValue FROM customer.
MESSAGE MaxBalanceValue
VIEW-AS ALERT-BOX INFO BUTTONS OK.
2. Using a FOR EACH block. For example:
DEFINE VARIABLE MaxBalanceValue AS DECIMAL NO-UNDO.
FOR EACH customer NO-LOCK FIELDS (balance) BREAK BY balance:
IF LAST(balance) THEN
MaxBalanceValue = balance.
END.
MESSAGE MaxBalanceValue
VIEW-AS ALERT-BOX INFO BUTTONS OK.
3. Using a FIND statement with the USE-INDEX option. This solution is the most efficient because it reads only one record. However, the field must be indexed. For example, if the field customer.balance is indexed in an ascending order, then the following code gets the maximum value of that field in the customer table:
DEFINE VARIABLE MaxBalanceValue AS DECIMAL NO-UNDO.
FIND LAST customer NO-LOCK USE-INDEX BalanceIndex.
ASSIGN
MaxBalanceValue = balance.
MESSAGE MaxBalanceValue
VIEW-AS ALERT-BOX INFO BUTTONS OK.