Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

Problem with decimal data type

 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 Krats... 116●7●7●15 accept rate: 0% 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 answered 17 Sep '19, 14:08 JBSchueler 3.3k●4●16●65 accept rate: 20% Volker Barth 40.5k●366●556●828 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
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• 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: 1,195 times

last updated: 18 Sep '19, 10:04