Quick Tip: Best practice for using sum range table command

  • Article

David Meego - Click for blog homepageI came across an interesting issue yesterday relating to the Dexterity sum range command. We had a SQL datetime conversion error caused by a sum range command. This was the error message:

A sum range operation on table 'SVC_Serial_Lot_Work_HIST' failed accessing SQL data.

More Info: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time

When looking at the logs we could see that the error was caused by the following convert function in the resulting query. For some reason SQL got upset with a negative year value in a date!!

CONVERT(datetime,'-7935.03.07')

Further research into the source code issuing the sum range command and looking at our problem report database identified the cause.

This post is to explain how the command works, how it should be used and what can happen when it Best Practice is not followed.

This command can be used to sum numeric values in table. For SQL tables, it leverages the T-SQL SUM() function and provides far better performance than using code to loop through the table and add up values.

The way that the sum range command works is described in detail in the Dexterity help, but there are some issues I have seen which are not covered in the help file.

In summary, when the sum range command is used, the current contents of the table buffer is checked and for each of the non zero fields, all of the records in the range (exclusive) are summed.

An exclusive range is a range where each of the key fields is checked separately to see if it falls within the limits for that field. So a record is only included in the sum if all the key fields are individually within the limits. For a "well defined" range, the selected records will be the same as an inclusive range (where a first and last record are selected and all records in between are included).

Now the help file mentions that you can sum numeric fields (integer, long integer and currency) but what it does not say is that fields of other datatypes can also be summed with unpredictable results. This problem is a known issue logged in our system as Problem Report 7380. However, if instructions in the help file are followed, this issue does not occur.

Here are the correct "Best Practice" steps for using the sum range command:

  1. Set the range on the table.
  2. Clear the table buffer.
  3. Set the fields to be summed to 1 (non-zero).
  4. Sum range.

If you follow these steps... great, but I have seen a number of times when step 2 has been missed and the table buffer is not cleared before setting which fields should be summed. This means that the fields used for setting the range are also included in the summing process.

The following are some examples of the results of summing additional fields:

  • Out of range issues when a long integer Sequence Number field has a result too large for the datatype.
     
  • Summed non numeric fields can cause corruption to other fields in the table buffer. I have seen non-printable characters in strings and negative values in date and numeric fields.

So the bottom line is:

AFTER SETTING THE RANGE, DON'T FORGET TO CLEAR THE TABLE BUFFER BEFORE SETTING THE FIELDS TO BE SUMMED.

Got it?, Good! :-)

David