# Thread: db2 values clause

1. Registered User
Join Date
Jan 2008
Posts
13

## 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. Registered User
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"

3. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
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`

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

5. Registered User
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.

#### Posting Permissions

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