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;