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

asked 16 Sep '19, 11:02

Robert%20Kratschmann's gravatar image

Robert Krats...
963410
accept rate: 0%

edited 16 Sep '19, 11:06

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

(17 Sep '19, 08:21) Bud Durland MRP
Replies hidden

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.

(17 Sep '19, 09:38) Robert Krats...

set option PUBLIC.precision = 120; and retry

permanent link

answered 17 Sep '19, 14:08

JBSchueler's gravatar image

JBSchueler
2.7k21251
accept rate: 18%

converted 18 Sep '19, 04:32

Volker%20Barth's gravatar image

Volker Barth
36.1k342501749

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
Your answer
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:

×3

question asked: 16 Sep '19, 11:02

question was seen: 316 times

last updated: 18 Sep '19, 10:04