![[spacer]](1p.gif){kind=small}
The sum range statement totals each field that has a numeric data type (integer, long integer and currency) in a specified range.
sum range table table_name
• table table_name – The name of the table containing the fields to be summed.
You must use the range statement to specify the range that will be used by the sum range statement. The sum range statement creates an exclusive range from the values specified by the range statement. In an exclusive range, each record’s key segments are evaluated individually, comparing each segment’s value to the value specified when the range was created. If any key field for a record is outside of the specified range for that key segment, the record will be excluded from the range used by the sum range statement. The following flowchart describes the process.
The sum range statement only totals numeric fields (integer, long integer and currency) that have non-zero values in the table buffer. After you establish the range using the range statement, you should specify which fields you want to sum by clearing the table buffer and setting the value of each of these fields to 1. You can then issue the sum range statement to total these fields.
|
Using the sum range statement instead of a loop construct can improve the speed and efficiency of applications that use the SQL database type. |
For example, you could have a key for the Invoice table that includes two key segments, Invoice_Number and Item_Number. If you use that key to create a range of all invoice numbers between 100 and 110, and all item numbers between 1 and 5, the following records would be included in the range created by the range statement for the Pervasive.SQL or c-tree database managers.
|
Invoice_Number |
Item_Number |
Item_Price |
|
100 |
1 |
10.00 |
|
101 |
2 |
65.50 |
|
102 |
6 |
42.00 |
|
103 |
1 |
27.75 |
|
103 |
2 |
38.25 |
Notice the record for invoice number 102. It’s included in the range, even though its corresponding item number is 6. It’s included because for the Pervasive.SQL and c-tree database managers, the range statement creates an inclusive range. Refer to the range statement for a complete description of how ranges are evaluated for the c-tree, Pervasive.SQL and SQL database managers.
If you use the sum range statement to sum the Item_Price field, the statement would first create an exclusive range based upon the range shown in the illustration. The record for Invoice 102 would not be included in this exclusive range, because even though 102 is within the specified range for the first key segment, 6 is outside of the defined range for the second key segment.
In the previous example, the summed value for the Item_Price field returned by the sum range statement would be $141.50. If you were using the Pervasive.SQL or c-tree database managers and had set a range only and used a loop construct to loop through the records in the range totaling the Item_Price field, the result would have been $183.50.
|
Keep these range differences in mind if you plan to replace loop constructs in existing applications with the sum range statement. Using the clear field and fill statements for all key segments after the first key segment is one way of ensuring that the ranges used by the range and sum range statements are the same for all database types. |
Depending upon the database type used, Dexterity responds differently when an overflow situation is encountered. If a SQL database type is used, an error is returned and no values will be summed. If the c-tree Plus or Pervasive.SQL database types are used, no error will be returned. Instead, currency fields will be returned at their maximum value, while integer and long integer fields will wrap within their allowable range of values. For example, if the sum of integer fields should be 33,750, the value -31,786 will be returned instead.
|
Be sure to test for overflow situations, especially if you are summing integer fields, large ranges, or fields with large numeric values. |