Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2008
    Posts
    13

    Smile Unanswered: db2 values clause

    Hello all, I am trying to use the db2 values clause
    for example: db2 values (30/70) and all it retruns is 0
    i was hoping to get in decimal answer can someone assist me.. thanks in advance
    sunny

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What else do you expect? 30 / 70 is 0. Since both input parameters are integers, you will get an integer division. That's how all major programming languages do that. If you want to have decimal or float/double precision division, you should do "30.0 / 70" or "30 / 70.0" or "30.0 / 70.0"
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze
    If you want to have decimal or float/double precision division, you should do "30.0 / 70" or "30 / 70.0" or "30.0 / 70.0"
    The last one will still return 0, at least when "30.0" is seen as a DECIMAL(3,1), since division of DECIMALs is essentially division of integers with appropriate before & after shifting of the decimal point.
    So, 30.0/70.0 = 300/700 = 0, while 30.0/70 = 300/70 "with shift 1" = 4 "with shift 1" = 0.4
    On the other hand, when "30.0" is seen as a float (not a fixed decimal), division is done in "arbitrary precision", and the result is returned as FLOAT, i.e., as something like 0.4285714286

    Now the question is: what does DB2 do when it sees 30.0: is it a DECIMAL of a FLOAT?
    If you want to be independent of this (at first sight arbitrary) choice, cast your input data yourself:
    Code:
    CAST(30 AS float) / 70
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    <sorry, duplicate>

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    30.0 is a DECIMAL(3, 1) as a describe reveals:
    Code:
    $ db2 "describe values 30.0"
    
    SQLDA Information
    
     sqldaid : SQLDA     sqldabc: 1136  sqln: 20  sqld: 1
    
     Column Information
    
     sqltype               sqllen  sqlname.data                    sqlname.length
     --------------------  ------  ------------------------------  --------------
     484   DECIMAL           3, 1  1                                            1
    But once the expression becomes more complex, the resulting data type differs:
    Code:
    $ db2 "describe values 30.0 / 70"
    
    SQLDA Information
    
     sqldaid : SQLDA     sqldabc: 1136  sqln: 20  sqld: 1
    
     Column Information
    
     sqltype               sqllen  sqlname.data                    sqlname.length
     --------------------  ------  ------------------------------  --------------
     484   DECIMAL          31,29  1                                            1
    
    $ db2 "values 30 / 70.0"
    
    1
    ---------------------------------
                0,4285714285714285714
    
      1 record(s) selected.
    
    $ db2 "values 30.0 / 70"
    
    1
    ---------------------------------
      0,42857142857142857142857142857
    
      1 record(s) selected.
    
    $ db2 "values 30.0 / 70.0"
    
    1
    ---------------------------------
       0,4285714285714285714285714285
    
      1 record(s) selected.
    I agree that this looks "arbitrary" at first glance. But I'm sure that there is some place in the documentation explaining the behavior. I just can't be bothered to search for this myself, since the answer to the original question is just that "30 / 70" is an integer division.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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