|
As the power function is in SQL Anywhere 17.0.10.5866 defined only for the double data type, I wrote a new power function only for integers called power_int: create or replace function power_int( @int decimal(127,0), @j decimal (127,0) ) returns decimal(127,0) as begin declare @i decimal (127,0) declare @a decimal (127,0) if @j=0.0 set @int=1.0 set @i=1.0 set @a=@int while @i<=@j-1.0 begin set @int=@int*@a set @i=@i+1.0 end return @int end; The function generates for smaller results correct values. If the result has more the 33 digits it becomes wrong: select power_int (16,27) --324518553658426726783156020576200 --wrong result --324518553658426726783156020576256 --correct select power_int (2,107) --162259276829213363391578010288100 --wrong result --162259276829213363391578010288128 --correct While power_int (16,26) and (2,106) gave the correct result. In ASE 16.0.x the function works fine if instead of decimal(127,0) decimal(38,0) will be used. Many thanks Robert |
|
set option PUBLIC.precision = 120; and retry Works fine, never heard about this option. Many thanks
(18 Sep '19, 02:44)
Robert Krats...
Replies hidden
Default precision and scale are PUBLIC database options that can be set. Check the documentation for more information.
(18 Sep '19, 10:04)
JBSchueler
|
Just curious -- why not use integer data types? I seem to recall Decimal only being accurate to 32 digits, but haven't researched it.
The bigint data type is only usable up to a maximum of 9,223,372,036,854,775,807 (19 digits). The procedure is part of a hexadecimal to decimal conversation with up to 32 digits. So bigint may be to small.