# Thread: How to get 15 decimal precision in SQL statement?

1. Registered User
Join Date
Jan 2003
Posts
1,629

## Unanswered: How to get 15 decimal precision in SQL statement?

Hi,
using db2 10.1 fixpack 1 on Linux I am trying to divide two numbers. The first test produces correct values. All other tests reduces precision. Why? I would like to have 15 decimal places after each of calculation bellow.
Is this data type conversion problem? How to write SQL to have exactly 15 decimal point precision?
Thanks

Code:
```values (dec(1,16,15) / dec(1.298314285714280,16,15))

1
---------------------------------
0,770229528399466

values (dec(1,17,15) / dec(1.298314285714280,17,15))

1
---------------------------------
0,77022952839946

values (dec(1,18,15) / dec(1.298314285714280,18,15))

1
---------------------------------
0,7702295283994

values (dec(1,19,15) / dec(1.298314285714280,19,15))

1
---------------------------------
0,770229528399

values (dec(1,20,15) / dec(1.298314285714280,20,15))

1
---------------------------------
0,77022952839

values (dec(1,30,15) / dec(1.298314285714280,30,15))

1
---------------------------------
0,7```

2. Registered User
Join Date
Apr 2006
Location
Belgium
Posts
2,539
Expressions - IBM DB2 9.7 for Linux, UNIX, and Windows
and at cast

3. Registered User
Join Date
Jan 2003
Posts
1,629
@przytula_guy, I have looked at cast. As I understand the following have the same meaning:
Code:
```values (dec(1.123456789012345, 16, 15) )
values (cast (1.123456789012345 as dec(16, 15)) )```
and this does not solve my problem.

What I am asking is why the following command always return 15 decimal points:
Code:
```values ( dec(1.12345678901234567890, 16,15) );
values ( dec(1.12345678901234567890, 17,15) );
values ( dec(1.12345678901234567890, 18,15) );
values ( dec(1.12345678901234567890, 19,15) );
values ( dec(1.12345678901234567890, 20,15) );
values ( dec(1.12345678901234567890, 21,15) );
values ( dec(1.12345678901234567890, 22,15) );
values ( dec(1.12345678901234567890, 23,15) );
values ( dec(1.12345678901234567890, 24,15) );
values ( dec(1.12345678901234567890, 25,15) );
values ( dec(1.12345678901234567890, 26,15) );
values ( dec(1.12345678901234567890, 27,15) );
values ( dec(1.12345678901234567890, 28,15) );
values ( dec(1.12345678901234567890, 29,15) );
values ( dec(1.12345678901234567890, 30,15) );
values ( dec(1.12345678901234567890, 31,15) );```
... result:
Code:
```1
------------------
1,123456789012345

1 record(s) selected.

1
-------------------
1,123456789012345

1 record(s) selected.

1
--------------------
1,123456789012345

1 record(s) selected.

1
---------------------
1,123456789012345

1 record(s) selected.

1
----------------------
1,123456789012345

1 record(s) selected.

1
-----------------------
1,123456789012345

1 record(s) selected.

1
------------------------
1,123456789012345

1 record(s) selected.

1
-------------------------
1,123456789012345

1 record(s) selected.

1
--------------------------
1,123456789012345

1 record(s) selected.

1
---------------------------
1,123456789012345

1 record(s) selected.

1
----------------------------
1,123456789012345

1 record(s) selected.

1
-----------------------------
1,123456789012345

1 record(s) selected.

1
------------------------------
1,123456789012345

1 record(s) selected.

1
-------------------------------
1,123456789012345

1 record(s) selected.

1
--------------------------------
1,123456789012345

1 record(s) selected.

1
---------------------------------
1,123456789012345

1 record(s) selected.```
So all of the results have exactly 15 decimal points like expected.

And why when dividing two numbers like:
Code:
```values ( dec(1, 16,15) / dec(1.12345678901234567890, 16,15) );
values ( dec(1, 17,15) / dec(1.12345678901234567890, 17,15) );
values ( dec(1, 18,15) / dec(1.12345678901234567890, 18,15) );
values ( dec(1, 19,15) / dec(1.12345678901234567890, 19,15) );
values ( dec(1, 20,15) / dec(1.12345678901234567890, 20,15) );
values ( dec(1, 21,15) / dec(1.12345678901234567890, 21,15) );
values ( dec(1, 22,15) / dec(1.12345678901234567890, 22,15) );
values ( dec(1, 23,15) / dec(1.12345678901234567890, 23,15) );
values ( dec(1, 24,15) / dec(1.12345678901234567890, 24,15) );
values ( dec(1, 25,15) / dec(1.12345678901234567890, 25,15) );
values ( dec(1, 26,15) / dec(1.12345678901234567890, 26,15) );
values ( dec(1, 27,15) / dec(1.12345678901234567890, 27,15) );
values ( dec(1, 28,15) / dec(1.12345678901234567890, 28,15) );
values ( dec(1, 29,15) / dec(1.12345678901234567890, 29,15) );
values ( dec(1, 30,15) / dec(1.12345678901234567890, 30,15) );
values ( dec(1, 31,15) / dec(1.12345678901234567890, 31,15) );```
I get eats-up of the decimal points:
Code:
```1
---------------------------------
0,890109890990219

1 record(s) selected.

1
---------------------------------
0,89010989099021

1 record(s) selected.

1
---------------------------------
0,8901098909902

1 record(s) selected.

1
---------------------------------
0,890109890990

1 record(s) selected.

1
---------------------------------
0,89010989099

1 record(s) selected.

1
---------------------------------
0,8901098909

1 record(s) selected.

1
---------------------------------
0,890109890

1 record(s) selected.

1
---------------------------------
0,89010989

1 record(s) selected.

1
---------------------------------
0,8901098

1 record(s) selected.

1
---------------------------------
0,890109

1 record(s) selected.

1
---------------------------------
0,89010

1 record(s) selected.

1
---------------------------------
0,8901

1 record(s) selected.

1
---------------------------------
0,890

1 record(s) selected.

1
---------------------------------
0,89

1 record(s) selected.

1
---------------------------------
0,8

1 record(s) selected.

1
---------------------------------
0,

1 record(s) selected.```
Decimal points gets eaten-up. I am expecting to have 15 decimal points.

Thanks

4. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
And why when dividing two numbers like:
Code:
```...
values ( dec(1, 29,15) / dec(1.12345678901234567890, 29,15) );
...```
I get eats-up of the decimal points:
Code:
```...

1
---------------------------------
0,89

1 record(s) selected.

...```
Decimal points gets eaten-up. I am expecting to have 15 decimal points.
Originally Posted by przytula_guy
You would see the following descriptions in that.
Decimal arithmetic in SQL

The following formulas define the precision and scale of the result of decimal operations in SQL.
The symbols p and s denote the precision and scale of the first operand,
and the symbols p' and s' denote the precision and scale of the second operand.

...

Multiplication
...

Division
The precision of the result of division is 31. The scale is 31-p+s-s'. The scale must not be negative.

Note: ...
...
For example:
Code:
```values ( dec(1, 29,15) / dec(1.12345678901234567890, 29,15) );
------------------------------------------------------------------------------

1
---------------------------------
0.89

1 record(s) selected.```
Code:
```DESCRIBE
values ( dec(1, 29,15) / dec(1.12345678901234567890, 29,15) );
------------------------------------------------------------------------------

Column Information

Number of columns: 1

SQL type              Type length  Column name                     Name length
--------------------  -----------  ------------------------------  -----------
484   DECIMAL               31, 2  1                                         1```
p = 29, s = 15, p' = 29, s' = 15
The precision of the result of division is 31.
The scale is 31-p+s-s' = 31-29+15-15 = 2.

5. Registered User
Join Date
Jan 2003
Posts
1,629
Hi,
I am astonished! Can't beleve that DB2 is working in this way. Is there any reason why? How to overcome this problem? I know I can make this calculation in my program, but is there any way to calculate this simple two numbers division in SQL and not getting some trunkated result?

I compared DB2 10.1 on Linux:
Code:
```SELECT
CAST(1 AS DEC(29,15))
/
CAST(1.12345678901234567890 AS DEC(29,15))
AS RESULT
FROM test;

RESULT
---------------------------------
0,89```
with MySQL 5.5 on Linux:
Code:
```SELECT
CAST(1 AS DEC(29,15))
/
CAST(1.123456789012345467890 AS DEC(29,15))
AS RESULT
FROM test;

+-----------------------+
| RESULT                |
+-----------------------+
| 0.8901098909902191115 |
+-----------------------+```
with PostgreSQL 9.1 on Linux:
Code:
```SELECT
CAST(1 AS DEC(29,15))
/
CAST(1.12345678901234567890 AS DEC(29,15))
AS RESULT
FROM test;
result
------------------------
0.89010989099021831919```
P.S. I thought this is a bug in DB2, but now it looks like a limitation of DB2.
Regards
Last edited by grofaty; 06-19-13 at 09:08.

6. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Code:
```SELECT
CAST(1 AS DEC(29,15))
/
CAST(1.12345678901234567890 AS DEC(29,15))
AS RESULT
FROM test;```
Why do you need to CAST(1 AS DEC(29,15))?
Please try by using CAST(1 AS DEC(16,15)) or CAST(1 AS DEC(2,1)).

I thought this is a bug in DB2, but now it looks like a limitation of DB2.
I supposed the reason might be to keep enough place of integer part of the result(in other words, to avoid OVERFLOW),
even if dividened was largest absolute value of that datatype and divisor was smallest absolute value of that datatype.

For example: Though the dividened was not largest absolute value...
Code:
```------------------------------ Commands Entered ------------------------------
SELECT
CAST(-12345678901234.0 AS DEC(29,15))
/
CAST(0.000000000000001 AS DEC(29,15))
AS RESULT
FROM  sysibm.sysdummy1
;
------------------------------------------------------------------------------

RESULT
---------------------------------
-12345678901234000000000000000.00

1 record(s) selected.```
Last edited by tonkuma; 06-20-13 at 05:31. Reason: Add "(in other words, to avoid OVERFLOW)"

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Originally Posted by grofaty
...
...

with MySQL 5.5 on Linux:
Code:
```SELECT
CAST(1 AS DEC(29,15))
/
CAST(1.123456789012345467890 AS DEC(29,15))
AS RESULT
FROM test;

+-----------------------+
| RESULT                |
+-----------------------+
| 0.8901098909902191115 |
+-----------------------+```
with PostgreSQL 9.1 on Linux:
Code:
```SELECT
CAST(1 AS DEC(29,15))
/
CAST(1.12345678901234567890 AS DEC(29,15))
AS RESULT
FROM test;
result
------------------------
0.89010989099021831919```
P.S. I thought this is a bug in DB2, but now it looks like a limitation of DB2.
Regards
The result of MySQL 5.5 was accurate until 14 decimal places, like 0.8901098909902191115
or, if exact result was rounded off to the 15th decimal place, the result of MySQL was accurate until 15 decimal places.
The result of PostgreSQL 9.1 was accurate until 15 decimal places, like 0.89010989099021831919

Compared with calculaion by DECFLOAT...
Code:
```------------------------------ Commands Entered ------------------------------
SELECT
CAST( 1 AS DECFLOAT)
/
CAST(1.12345678901234567890 AS DECFLOAT)
AS RESULT
FROM  sysibm.sysdummy1
;
------------------------------------------------------------------------------

RESULT
------------------------------------------
0.8901098909902185735958205673806026

1 record(s) selected.```

8. Registered User
Join Date
Jan 2003
Posts
1,629
Originally Posted by tonkuma
Compared with calculaion by DECFLOAT...
Code:
```------------------------------ Commands Entered ------------------------------
SELECT
CAST( 1 AS DECFLOAT)
/
CAST(1.12345678901234567890 AS DECFLOAT)
AS RESULT
FROM  sysibm.sysdummy1
;
------------------------------------------------------------------------------

RESULT
------------------------------------------
0.8901098909902185735958205673806026

1 record(s) selected.```
Your result is 100% correct. You can check this out with Windows Calculator and I get the same number. But executing your SQL and I get: +8,90109890990219E-001

And now my one million dollar question is how did you convert number: +8,90109890990219E-001 to get: 0.8901098909902185735958205673806026?

9. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
But executing your SQL and I get: +8,90109890990219E-001
It is very strange, if you executed exactly same as my SQL staement.
Because, I didn't use float datatype. I used DECFLOAT(or DECIMAL) datatype.
So, result datatype must be DECFLOAT(or DECIMAL).

I already showed DECFLOAT division returned DECFLOAT result.
I didn't saw any float intermediate results.

Although, I don't know DB2 inernal,
I guessed that DB2 would use DECIMAL operations inside of DB2, for DECIMAL dataype.
If underlying hardware didn't support DECIMAL machine instructions, DB2 would simulate them.
So might be for DECFLOAT.

10. Registered User
Join Date
Jan 2003
Posts
1,629
Originally Posted by tonkuma
It is very strange, if you executed exactly same as my SQL staement.
Now I have found out what is the problem. I was accessing DB2 v10 on Linux from my DB2 v8.2 Windows client and the result was as described a float. So it looks like some old client new server problem. I need this DB2 v8.2 client because newer versions of DB2 client is not compatible with one software that is must in my case.

Now I have tried to access DB2 v10 server from DB2 v10 client and the result is as you described. So problem solved.

Thanks a lot for help.
Last edited by grofaty; 06-24-13 at 08:37.

#### Posting Permissions

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