Problem with decimal data type

[robert_kratschmann]

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

[BudDurland]

Just curious -- why not use integer data types? I seem to recall Decimal only being accurate to 32 digits, but haven't researched it.

[robert_kratschmann]

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.

[jack_schueler · Accepted solution]

set option PUBLIC.precision = 120; and retry

[robert_kratschmann · Accepted solution]

Works fine, never heard about this option.

Many thanks

[jack_schueler · Accepted solution]

Default precision and scale are PUBLIC database options that can be set. Check the documentation for more information.