SQLA 17.0.4 Build 2053 (also seen in 12 and 16 though) It appears that fields defined as decimal (30,6) can store more than 6 decimal places.

Take this example:

drop table if exists zMyRate;
create table zMyRate (
    ID integer default autoincrement, 
    Rate dec(30,6) NOT NULL,
    PRIMARY KEY (ID ASC))
    ;

insert into zMyRate (Rate) 
VALUES 
(1.234567), 
(8.901234), 
(5.678901),
(2.34567899)
;

SELECT ID, Rate, cast(Rate as dec(30,8)) as ExtraDecRate FROM zMyRate;
ID,Rate,ExtraDecRate
1,1.234567,1.23456700
2,8.901234,8.90123400
3,5.678901,5.67890100
4,2.345679,2.34567900

The last two digits on the fourth value are truncated during the insert as I would expect. But if you update these with a calculation that is not casted, the results are stored:

Update zMyRate
set Rate = Rate * 1.3456
;

SELECT ID, Rate, cast(Rate as dec(30,8)) as ExtraDecRate FROM zMyRate;
ID,Rate,ExtraDecRate
1,1.661233,1.66123336
2,11.977500,11.97750047
3,7.641529,7.64152919
4,3.156346,3.15634566

The extra decimal places are only returned if you cast the field as something defined with the extra decimals but not on the normal select as in column 2.

What is interesting is that if you alter the table and define the field as decimal (12,6), this behavior stops and the numbers are truncated in both situations - insert and update with calculation.

asked 02 Jun, 16:29

Siger%20Matt's gravatar image

Siger Matt
3.2k496997
accept rate: 13%

I'm guessing this has something to do with the fact the exact same storage is used for values that differ by one digit of actual precision and/or scale.

The formula for storage requirements is thus, where p and s are the actual precision and scale of the value, not the declared precision and scale:

2 + TRUNCNUM ( ( p - s + 1 ) / 2, 0 ) + TRUNCNUM ( ( s + 1 ) / 2, 0 )

You can use DATALENGTH() as a check on your work.

AFAIK this is the old-school "packed decimal" format where each digit is packed into 4 bits, but the field takes up a multiple of 8 bits, so half the time there's an extra nybble (nybble, get it? smaller than a byte? :)

BEGIN
DECLARE d5  DECIMAL ( 30, 5 );
DECLARE d6  DECIMAL ( 30, 6 );
SET d5  = 9.123;
SET d6  = 9.1234;
SELECT d5,
       DATALENGTH ( d5 ), 
       4 as p5, 
       3 as s5, 
       2 + TRUNCNUM ( ( p5 - s5 + 1 ) / 2, 0 ) + TRUNCNUM ( ( s5 + 1 ) / 2, 0 ) AS d5_length;
SELECT d6,
       DATALENGTH ( d6 ), 
       5 as p6, 
       4 as s6, 
       2 + TRUNCNUM ( ( p6 - s6 + 1 ) / 2, 0 ) + TRUNCNUM ( ( s6 + 1 ) / 2, 0 ) AS d6_length;
END;
                              d5 DATALENGTH(d5)     p5     s5 d5_length 
-------------------------------- -------------- ------ ------ --------- 
                         9.12300              5      4      3         5

                              d6 DATALENGTH(d6)     p6     s6 d6_length 
-------------------------------- -------------- ------ ------ --------- 
                        9.123400              5      5      4         5 

I know this doesn't answer your question, but maybe it gets you further along the way :)

(03 Jun, 05:11) Breck Carter
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×2
×1

question asked: 02 Jun, 16:29

question was seen: 229 times

last updated: 03 Jun, 05:11