Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    8

    Unanswered: Question about ROUND function in DB2v9.5 LUW

    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?
    Last edited by mtangshan; 05-20-10 at 12:11.

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation

    Quote Originally Posted by mtangshan View Post
    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

  3. #3
    Join Date
    Feb 2009
    Posts
    8
    The create table statement is

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

    Thanks for your reply!

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    I told you: "DB2 made EASY" ...

    Lenny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •