|
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. |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- 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:
question asked: 02 Jun '17, 16:29
question was seen: 1,414 times
last updated: 03 Jun '17, 05:11
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 5I know this doesn't answer your question, but maybe it gets you further along the way :)