The issue of "truncation error occurred"
Penggunaan opsi set arithabort numeric_truncation
We can actually ignore the scale truncation errors via 'set arithabort numeric_truncation off' command.
This
also gives a better picture as why the errors were thrown and actually
shows us that how many scale values we lost when implicit conversions
were made ( good debugging )
Also even if the truncation error
occurs, the batch sql continues to execute so might be that the job
which executed the procedure was not failing but the data where this
truncation is happening, was not getting displayed.
Here are some examples and comments:
Case 1 :
1>
select (convert(numeric(17,6),10177.244681) *
convert(numeric(35,12),947.8281139625) / 100) *
convert(numeric(28,13),1.4556537149915)
2>
Truncation error occurred.
Command has been aborted.
1> set arithabort numeric_truncation off
2>
1>
select (convert(numeric(17,6),10177.244681) *
convert(numeric(35,12),947.8281139625) / 100) *
convert(numeric(28,13),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.41325534436901151728336040
(1 row affected)
1> set arithabort numeric_truncation on ( this is ON by default thus we get errors in scale losses )
2>
1>
select (convert(numeric(17,6),10177.244681) *
convert(numeric(35,12),947.8281139625) / 100) *
convert(numeric(28,18),1.4556537149915)
2>
140416.4132553443690115172833604056875
-- for original setting, we were loosing out 5 scale values, hence when
we made the scale as 18, no truncation occurred.
Case 2
1>
select (convert(numeric(17,6),10177.244681) *
convert(numeric(35,12),947.828113962515) / 100) *
convert(numeric(35,20),1.45565371499151111111)
2>
Truncation error occurred.
Command has been aborted.
1> set arithabort numeric_truncation off
2>
1>
select (convert(numeric(17,6),10177.244681) *
convert(numeric(35,12),947.828113962515) / 100) *
convert(numeric(35,20),1.45565371499151111111)
2>
--------------------------------------------------------------------------------
140416.41325534766300175115789757
Lets find out how much it goes..
1> select 10177.244681*(947.828113962515/100)*1.45565371499151111111
2>
---------------------------------------------------------------
140416.4132553476630017511578975709032198096365000000000
Its going beyond 38 which is the max scale we can have on numeric and hence truncating…
Case 3 :
In truncation errors being off :
1>
select (convert(numeric(17,6),10177.244681) *
convert(numeric(35,12),947.8281139625) / 100) *
convert(numeric(35,20),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.41325534436901151728336040
We know that this multiplication goes to 56875 as per Case 1 so adding 5 more to scale would lead to no scale loss
1>
select (convert(numeric(17,6),10177.244681) *
convert(numeric(35,12),947.8281139625) / 100) *
convert(numeric(35,25),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.4132553443690115172833604056875
1> set arithabort numeric_truncation on
2>
1>
select (convert(numeric(17,6),10177.244681) *
convert(numeric(35,12),947.8281139625) / 100) *
convert(numeric(35,25),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.4132553443690115172833604056875
We definetely do not need that length of precesions and can safely ignore the truncation errors..
Hope this helps.
