Loosing decimal values while selecting in Oracle
-
Hi all, here is my SP create FUNCTION fn_Ceiling ( Source IN DECIMAL ) RETURN DECIMAL as DecDiff DECIMAL(16,3); Ceiling DECIMAL(16,3); Source1 DECIMAL; BEGIN SELECT NVL(Source, 0) INTO Source1 FROM DUAL; SELECT Source1 - FLOOR(Source1) INTO DecDiff FROM DUAL; SELECT FLOOR(Source) + case WHEN DecDiff between 0.000 and 0.250 THEN 0.000 WHEN DecDiff between 0.250 and 0.750 THEN 0.500 WHEN DecDiff between 0.750 and 1.000 THEN 1.000 else DecDiff end INTO Ceiling FROM DUAL; return Ceiling; END; / It is created... but if i execute the function am not getting the correct value ex:- select fn_ceiling(1.75) from dual; am getting the result as 1 (actaul result should be 1.500) Dnt know what i did wrong?
-
Hi all, here is my SP create FUNCTION fn_Ceiling ( Source IN DECIMAL ) RETURN DECIMAL as DecDiff DECIMAL(16,3); Ceiling DECIMAL(16,3); Source1 DECIMAL; BEGIN SELECT NVL(Source, 0) INTO Source1 FROM DUAL; SELECT Source1 - FLOOR(Source1) INTO DecDiff FROM DUAL; SELECT FLOOR(Source) + case WHEN DecDiff between 0.000 and 0.250 THEN 0.000 WHEN DecDiff between 0.250 and 0.750 THEN 0.500 WHEN DecDiff between 0.750 and 1.000 THEN 1.000 else DecDiff end INTO Ceiling FROM DUAL; return Ceiling; END; / It is created... but if i execute the function am not getting the correct value ex:- select fn_ceiling(1.75) from dual; am getting the result as 1 (actaul result should be 1.500) Dnt know what i did wrong?
Not sure with Oracle but I would look at Source1, no presision is declared. Also your case logic is going to cause a problem with 25 and 75. It meets 2 conditions is should be
WHEN DecDiff between 0.000 and 0.250 THEN 0.000
WHEN DecDiff between 0.251 and 0.750 THEN 0.500
WHEN DecDiff between 0.751 and 1.000 THEN 1.000Never underestimate the power of human stupidity RAH
-
Not sure with Oracle but I would look at Source1, no presision is declared. Also your case logic is going to cause a problem with 25 and 75. It meets 2 conditions is should be
WHEN DecDiff between 0.000 and 0.250 THEN 0.000
WHEN DecDiff between 0.251 and 0.750 THEN 0.500
WHEN DecDiff between 0.751 and 1.000 THEN 1.000Never underestimate the power of human stupidity RAH