Skip to Content

ROUND function and FLOAT values

Hello,

We use Sap Hana Express 2.00.021.00.1507025078 (fa/hana2sp02).

We are converting our Informationsystem from Oracle/SQLServer to Hana.

Now we run into a Problem with the round() Function.

We have to simulate the Oracle trunc(number) Function and we decided to use round(,,ROUND_DOWN).

This works well (for example)in:

select

round (9200.2,5,ROUND_DOWN),

LABS_FMT.TRUNCATEXORIGINAL(9200.2, 2,0,3)

from dummy;

Result:

ROUND(9200.2,5,ROUND_DOWN);LABS_FMT.TRUNCATEXORIGINAL(9200.2,2,0,3)

9.200,2;9.200,2

BUT in SOME SPECIAL Cases we get

select

round (9200.8,5,ROUND_DOWN),

LABS_FMT.TRUNCATEXORIGINAL(9200.8, 2,0,3)

from dummy;

Result:

ROUND(9200.8,5,ROUND_DOWN);LABS_FMT.TRUNCATEXORIGINAL(9200.8,2,0,3)

9.200,8;9.200,79999

The Function is listed below.

The Problem does occur on values like 9200.8 and 9.2008

We think that the problem in the function is caused by an cast of the round() function to a float value.

Do you have an idea what we can do?

CREATE FUNCTION LABS_FMT.TRUNCATEXORIGINAL (

in p_val FLOAT(53), -- Value to format

in p_trunc DECIMAL(2,0), -- truncating offset

in p_sd DECIMAL(2,0), -- Significant Digits

in p_dp DECIMAL(2,0)) -- Decimal Places

RETURNS r_result FLOAT(53)

AS

BEGIN

DECLARE v_sd DECIMAL(3,0);

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN

r_result:=null;

END;

IF p_val = 0 THEN

r_result:= p_val;

return;

END IF;

IF p_sd > 0 THEN

-- truncate to significant digits

v_sd := p_sd - FLOOR(LOG(10, ABS(p_val))) - 1;

IF (p_dp >= 0) AND

(v_sd > p_dp) THEN

-- truncate to decimal_places

r_result:= round(p_val, p_dp + p_trunc,ROUND_DOWN);

ELSE

r_result:= round(p_val, v_sd + p_trunc,ROUND_DOWN);

END IF;

ELSEIF p_dp >= 0 THEN

-- truncate to decimal_places

r_result:= round(p_val, p_dp + p_trunc,ROUND_DOWN);

ELSE

r_result:=p_val;

END IF;

return;

END;

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers