db2 values clause

db2link · #1 (**permalink**) 03-21-08, 13:38

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

stolze · #2 (**permalink**) 03-21-08, 13:43

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"

Peter.Vanroose · #3 (**permalink**) 03-22-08, 11:50

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 · #4 (**permalink**) 03-22-08, 11:56

<sorry, duplicate>

stolze · #5 (**permalink**) 03-23-08, 12:15

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech