PRB: SUM in SQL Does Not Return Number with Decimal Places

• Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6, 2.6a
• Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, 2.5b, 2.6, 2.6a
• Microsoft FoxPro for Macintosh, versions 2.5b, 2.5c, 2.6a
• Microsoft FoxPro for UNIX, version 2.6

SYMPTOMS

When using the SUM, MIN, or MAX commands in an SQL SELECT statement with the conditional IIF command, sometimes the resulting expression will not return the correct number of decimal positions. This is a problem only when the number returned should have decimal places but does not. For example, the following command returns an integer number if the first record does not meet the true condition of the IIF statement.

   SELECT SUM(IIF(ytdpurch > 20, ytdpurch, 0)) FROM customer

CAUSE

The SELECT statement looks at the format of the number after reading the first record. Because the first record in this case doesn't meet the true part of the IIF condition, a zero is placed as the first number to be summed because that is the false part of the IIF condition. The format of the 0 in the conditional IIF doesn't have decimal places. Therefore the format for that field in the query will not have decimal places.

WORKAROUND

If the false part of the IIF is given decimal places, such as 0.00, the SUM statement of the SELECT command will have the correct numerical structure.

STATUS

This behavior is by design.