I worte a program to test the ROUND function, However some behaviors confused me much. The following the my program and output:

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>

int main(int argc, char* argv[])
{
EXEC SQL INCLUDE SQLCA;

long lRetCode = 0L;

EXEC SQL BEGIN DECLARE SECTION;
double AY;
EXEC SQL END DECLARE SECTION;

EXEC SQL CONNECT TO ABCTest USER 123 USING 123 ;
EXEC SQL DELETE FROM T1 where 1=1;

AY = 100.15;
EXEC SQL INSERT INTO T1 VALUES (1, 100.174);
EXEC SQL INSERT INTO T1 VALUES (2, 100.175);
EXEC SQL INSERT INTO T1 VALUES (3, 100.176);


AY = 100.154;
EXEC SQL INSERT INTO T1 VALUES (5, 100.17);
EXEC SQL UPDATE T1 set val = val + ROUND(:AY, 2) where id = 5;
EXEC SQL INSERT INTO T1 VALUES (6, 100.17);
EXEC SQL UPDATE T1 set val = ROUND(val + :AY, 2) where id = 6;

AY = 100.155;
EXEC SQL INSERT INTO T1 VALUES (7, 100.17);
EXEC SQL UPDATE T1 set val = val + ROUND(:AY, 2) where id = 7;
EXEC SQL INSERT INTO T1 VALUES (8, 100.17);
EXEC SQL UPDATE T1 set val = ROUND(val + :AY, 2) where id = 8;

AY = 100.156;
EXEC SQL INSERT INTO T1 VALUES (9, 100.17);
EXEC SQL UPDATE T1 set val = val + ROUND(:AY, 2) where id = 9;
EXEC SQL INSERT INTO T1 VALUES (10, 100.17);
EXEC SQL UPDATE T1 set val = ROUND(val + :AY, 2) where id = 10;

AY = 100.154;
EXEC SQL INSERT INTO T1 VALUES (11, 100.18);
EXEC SQL UPDATE T1 set val = val + ROUND(:AY, 2) where id = 11;
EXEC SQL INSERT INTO T1 VALUES (12, 100.18);
EXEC SQL UPDATE T1 set val = ROUND(val + :AY, 2) where id = 12;

AY = 100.155;
EXEC SQL INSERT INTO T1 VALUES (13, 100.18);
EXEC SQL UPDATE T1 set val = val + ROUND(:AY, 2) where id = 13;
EXEC SQL INSERT INTO T1 VALUES (14, 100.18);
EXEC SQL UPDATE T1 set val = ROUND(val + :AY, 2) where id = 14;

AY = 100.156;
EXEC SQL INSERT INTO T1 VALUES (15, 100.18);
EXEC SQL UPDATE T1 set val = val + ROUND(:AY, 2) where id = 15;
EXEC SQL INSERT INTO T1 VALUES (16, 100.18);
EXEC SQL UPDATE T1 set val = ROUND(val + :AY, 2) where id = 16;

EXEC SQL INSERT INTO T1 VALUES (17, 0.00);
AY = 100.154;
EXEC SQL UPDATE T1 set val = ROUND(:AY, 2) where id = 17;

EXEC SQL INSERT INTO T1 VALUES (18, 0.00);
AY = 100.155;
EXEC SQL UPDATE T1 set val = ROUND(:AY, 2) where id = 18;

EXEC SQL INSERT INTO T1 VALUES (19, 0.00);
AY = 100.156;
EXEC SQL UPDATE T1 set val = ROUND(:AY, 2) where id = 19;

//---------------
EXEC SQL INSERT INTO T1 VALUES (20, 0.00);
AY = 100.164;
EXEC SQL UPDATE T1 set val = ROUND(:AY, 2) where id = 20;

EXEC SQL INSERT INTO T1 VALUES (21, 0.00);
AY = 100.165;
EXEC SQL UPDATE T1 set val = ROUND(:AY, 2) where id = 21;

EXEC SQL INSERT INTO T1 VALUES (22, 0.00);
AY = 100.166;
EXEC SQL UPDATE T1 set val = ROUND(:AY, 2) where id = 22;

//---------------
EXEC SQL INSERT INTO T1 VALUES (23, 0.00);
AY = 100.174;
EXEC SQL UPDATE T1 set val = ROUND(:AY, 2) where id = 23;

EXEC SQL INSERT INTO T1 VALUES (24, 0.00);
AY = 100.175;
EXEC SQL UPDATE T1 set val = ROUND(:AY, 2) where id = 24;

EXEC SQL INSERT INTO T1 VALUES (25, 0.00);
AY = 100.176;
EXEC SQL UPDATE T1 set val = ROUND(:AY, 2) where id = 25;

EXEC SQL COMMIT;

}

And the output is :


ID VAL
----------- ------------------
1 100.17
2 100.17
3 100.17
5 200.32
6 200.32
7 200.32
8 200.32
9 200.33
10 200.33
11 200.33
12 200.33
13 200.33
14 200.34
15 200.34
16 200.34
17 100.15
18 100.15
19 100.16
20 100.16
21 100.17
22 100.17
23 100.17
24 100.17
25 100.18


24 record(s) selected.

Would you please tell me how the Line 18 and 25 is ROUNDing compared with line 21? Also line 7 and 8?

I want to see CREATE table T1 statement. It's depend on how you define VAL column.

Lenny

The create table statement is

create table T1 ( id int, val decimal(16,2));

Thanks for your reply!

I told you: "DB2 made EASY" ...

Lenny