Retrieving Data With SQL › Understanding CA Culprit Decimal Point Handling

Understanding CA Culprit Decimal Point Handling

In our STOCKS table example, the CLOSE_PRICE field is defined as having three implied decimal digits.

Table Definition

CREATE TABLE INV.STOCKS
(COMPANY_NAME  CHAR(20) NOT NULL,
 COMPANY_ID    CHAR(5),
 TRADING_DATE  DATE,
 VOLUME        INTEGER,
 CLOSE_PRICE   DECIMAL(9,3));

This allows you to represent 1/8 point fractions as a decimal value. However when formatting reports, you often want to print the value as a money field, dollars and cents. This means converting the number from DP=3 to DP=2.

To obtain the correctly rounded value, move the CLOSE_PRICE field to a work field, WK_CLOSE defined with two decimal points. Then, reference WK_CLOSE on the detail line. The report below shows why:

Report Syntax

col. 2
▼
IN  DB(Q)  DICTIONARY=TSTDICT   SCHEMA=INV
SQL SELECT * FROM STOCKS;
01OUT 80  D
010  WK_CLOSE  0.00
0151*001  COMPANY_NAME      HF
0151*002  CLOSE_PRICE       HF
0151*003  CLOSE_PRICE       DP=2   HH 'DP=2 ON' '5  LINE'
0151*004  WK_CLOSE          HF
017       MOVE CLOSE_PRICE TO WK_CLOSE

Report Output

                                           DP=2 ON
        COMPANY_NAME      CLOSE_PRICE      5  LINE       WK_CLOSE

   CA                          9.125        91.25           9.13
   Boston Edison               20.375       203.75          20.38

As you can see, coding CLOSE_PRICE DP=2 on the edit parameter changes the location of the implied decimal point, and produces erroneous results. To insure correct rounding, move the value to a work variable that was defined with the correct number of decimal points.