Professional Web Applications Themes

strange SQL behaviour - IBM DB2

Hello, I am running DB2 v7.2 on NT. suppose I ussue following simple SQL stmt: select 3/18*18, dec(3,8,3)/dec(18,8,3)*dec(18,8,3), dec(3,31,8)/dec(18,31,8)*dec(18,31,8), dec(3,30,8)/dec(18,30,8)*dec(18,30,8) from sysibm.sysdummy1 The results are 3 1.8 Why doesn't the dec(..,31,8) give the correct result ? Can anyone explain this ? 30,8 works. 29,8 works, ........ Thanks in advance Peter -- Posted via [url]http://dbforums.com[/url]...

  1. #1

    Default strange SQL behaviour


    Hello,



    I am running DB2 v7.2 on NT.



    suppose I ussue following simple SQL stmt:



    select

    3/18*18,

    dec(3,8,3)/dec(18,8,3)*dec(18,8,3),

    dec(3,31,8)/dec(18,31,8)*dec(18,31,8),

    dec(3,30,8)/dec(18,30,8)*dec(18,30,8)

    from sysibm.sysdummy1



    The results are





    3



    1.8



    Why doesn't the dec(..,31,8) give the correct result ?

    Can anyone explain this ?

    30,8 works. 29,8 works, ........





    Thanks in advance





    Peter


    --
    Posted via [url]http://dbforums.com[/url]
    platho Guest

  2. #2

    Default Re: strange SQL behaviour

    That worked exactly as designed.
    Please see "SQL Reference, Chapter 2. Language elements, Expressions
    with arithmetic operatiors"

    SELECT *
    FROM (VALUES
    ('1. Operands', 'Integer', 'DEC(8,3)', 'DEC(31,8)',
    'DEC(30,8)')
    , ('31. Result'
    , CHAR(3/18*18)
    , CHAR(dec(3, 8,3)/dec(18, 8,3)*dec(18, 8,3))
    , CHAR(dec(3,31,8)/dec(18,31,8)*dec(18,31,8))
    , CHAR(dec(3,30,8)/dec(18,30,8)*dec(18,30,8))
    )
    , ('21. Mid Result'
    , CHAR(3/18)
    , CHAR(dec(3, 8,3)/dec(18, 8,3))
    , CHAR(dec(3,31,8)/dec(18,31,8))
    , CHAR(dec(3,30,8)/dec(18,30,8))
    )
    , ('20. DEC(p,s)/DEC(p'',s'') -> DEC(31 , 31-p-s+s'')'
    , 'Large integer'
    , '(8,3)/(8,3)->DEC(31,23)'
    , '(31,8)/(31,8)->DEC(31,0)'
    , '(30,8)/(30,8)->DEC(31,1)'
    )
    , ('30. DEC(p,s)*DEC(p'',s'') -> DEC(min(31,p+p'') ,
    min(31,s+s''))'
    , 'Large integer'
    , '(31,23)*(8,3)->DEC(31,26)'
    , '(31,0)*(31,8)->DEC(31,8)'
    , '(31,1)*(30,8)->DEC(31,9)'
    )
    ) AS t
    ORDER BY 1
    ;
    ---------------------------------------------------

    1 2
    3 4
    5
    -----------------------------------------------------------
    ------------- ---------------------------------
    --------------------------------- ---------------------------------
    1. Operands Integer
    DEC(8,3) DEC(31,8)
    DEC(30,8)
    20. DEC(p,s)/DEC(p',s') -> DEC(31 , 31-p-s+s') Large
    integer (8,3)/(8,3)->DEC(31,23) (31,8)/(31,8)->DEC(31,0)
    (30,8)/(30,8)->DEC(31,1)
    21. Mid Result 0
    00000000.16666666666666666666666 0000000000000000000000000000000.
    000000000000000000000000000000.1
    30. DEC(p,s)*DEC(p',s') -> DEC(min(31,p+p') , min(31,s+s')) Large
    integer (31,23)*(8,3)->DEC(31,26) (31,0)*(31,8)->DEC(31,8)
    (31,1)*(30,8)->DEC(31,9)
    31. Result 0
    00002.99999999999999999999988000 00000000000000000000000.00000000
    0000000000000000000001.800000000

    5 record(s) selected.
    Tokunaga T. Guest

Similar Threads

  1. Strange Template Behaviour
    By ROGM in forum Macromedia Contribute General Discussion
    Replies: 1
    Last Post: December 26th, 11:16 PM
  2. Strange behaviour of ASP library
    By Marcin Zmyslowski in forum ASP Components
    Replies: 1
    Last Post: May 11th, 04:36 AM
  3. Very strange behaviour
    By Mark in forum AIX
    Replies: 0
    Last Post: September 1st, 04:19 PM
  4. Strange CLI behaviour
    By Frank Passek in forum PHP Development
    Replies: 0
    Last Post: August 10th, 07:45 PM
  5. strange subquery behaviour?
    By HSalim in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 4th, 06:59 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139