As the power function is in SQL Anywhere 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
set @int=@int*@a
set @i=@i+1.0
return @int

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


asked 16 Sep '19, 11:02

Robert%20Kratschmann's gravatar image

Robert Krats...
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

accept rate: 20%

converted 18 Sep '19, 04:32

Volker%20Barth's gravatar image

Volker Barth

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 16 Sep '19, 11:02

question was seen: 955 times

last updated: 18 Sep '19, 10:04