NVL(7.50, 0) or TO_CHAR(7.50, 0) give 7.5 instead of 7.50

[ratjetoes]

Hello,

I created a function which calculates using significant numbers. For example, when you add 65 + 2.17 the result would be 67. Or 6.221 * 5.34 = 33.2. The problem is however, when I have a number which has an ending digit of 0, it get's lost in the conversion. Therefor, if you use 7.50 and multiply it with 8.321 the least number of significant numbers is 2 instead of 3. How can I fix this that 7.50 is read as 7.50 and not 7.5? Btw, are there any Oracle system functions which give you results according to the rules of significant numbers?

T.i.a.,
ratjetoes.

 

[SantaMufasa]

Ratjetoes,

Please forgive my mathematical ignorance/dimness/rustiness, but please remind me the "Rules of Significance" that apply in your case.

Are you using straight SQL, or are you using PL/SQL?


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[ratjetoes]

Hi Mufasa,

The rules of significance i use are these:
When adding or subtracting the result will have the number of digits equal to the lowest number of digits of the values used.

For example 65 (0 digits) + 2.17 (2 digits) gives 67 (lowest number of digits of values used is 0).

When multiplying or dividing the result will have the same number of significance numbers equal to the lowest number of significance numbers of the values used.

For example 6.211 (4 significance numbers) * 5.34 (3 significance numbers) gives 33.2 (3 significance numbers).

The problem however is that i need to calculate the number of significance numbers of a given value.

I use LENGTH(TO_CHAR(NVL(number, 0)) (and a REPLACE of course for removing the .) but when the number is 7.50 it doesn't "see" the last digit because 7.50 is converted as 7.5.

I'm using PL/SQL.

Btw, I totally agree with your sign.

 

[jimirvine]

Maybe a quick demo:

SQL> select to_char(7.5,'0')
  2  from dual;

TO
--
 8

SQL> ed
Wrote file afiedt.buf

  1  select to_char(7.5,0)
  2* from dual
SQL> /

TO
--
 8

SQL> ed
Wrote file afiedt.buf

  1  select to_char(7.5,'00')
  2* from dual
SQL> /

TO_
---
 08

SQL> ed
Wrote file afiedt.buf

  1  select to_char(7.5,'00.0')
  2* from dual
SQL> /

TO_CH
-----
 07.5

SQL> ed
Wrote file afiedt.buf

  1  select to_char(7.5,'00.00')
  2* from dual
SQL> /

TO_CHA
------
 07.50

 

[SantaMufasa]

Yes, Jim, but how does one programmatically produce the correct to_char format mask to meet the "Rules of Significance"? I believe that Ratjetoes is looking for a dynamic format mask, which produces output based upon the changing precision of the operands in numeric expressions (right, Ratjetoes?).

For example, following are a couple of rows of data, first with Oracle's natural output, then a facsimile of what (I believe) Ratjetoes wants:

select * from rat;

         X          Y
---------- ----------
     6.211       5.34
        65       2.17

select x * y from rat;

(Oracle's natural output):
       X*Y
----------
  33.16674
    141.05

(What [b]Ratjetoes[/b] wants):
       X*Y
----------
     33.17
       141

I've been puzzling over a way to make this happen since Ratjetoes first posted his need. <scratching head>


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[jimirvine]

Serves me right for not reading fully the question.
Answer number 1 is that the 'numbers' must be stored/passed as strings. storing/passing them as numbers will normalise the number and instantly become useless for anything with trailing zeros.
after that, a little bit of arithmetic (an 'interesting' proposition on a Sunday morning with a hangover )
It may need 'tweaking' to get the exact results, but the basic premise is there:

DROP TABLE nums PURGE;
CREATE TABLE nums (x VARCHAR2(10), y VARCHAR2(10));
INSERT INTO nums VALUES('6.211','5.34');
INSERT INTO nums VALUES('65','2.17');

WITH t AS (SELECT x, y, LEAST(LENGTH(REPLACE(x,'.',NULL)), LENGTH(REPLACE(y,'.',NULL))) a
                  ,LENGTH(TRUNC(x*y)) b
           from nums)
SELECT x, y
       ,TO_CHAR(x*y,DECODE(SIGN(a-b),1,LPAD('0',b,'0')||'.'||RPAD('0',a-b,'0'),LPAD('0',b,'0'))) result
FROM t
/

 

[ratjetoes]

hi jim, mustafa,

tnx 4 your answers. unfortunately te numbers are stored as NUMBER fields in the database.

the function i came up with (well this is my first oracle project so it's a bit longer then jim's answer):

// Start Calculate function
// Operator values: * / + -
// Replacement of both . and ,

FUNCTION Calculate(FirstNumber IN NUMBER, SecondNumber IN NUMBER, MATH_OPERATOR IN VARCHAR2) 
    RETURN NUMBER 
  AS
    math_length    NUMBER; -- length without digits
    math_result    NUMBER; -- of operation
    math_scale     NUMBER; -- total length
    math_precision NUMBER; -- number of digits
  BEGIN
    IF firstnumber IS NULL AND secondnumber IS NULL THEN
      RETURN NULL;
    ELSE
      IF math_operator = '*' OR math_operator = '/'
      THEN
        -- No divide by zero
        IF math_operator = '/' AND (secondnumber IS NULL OR secondnumber = 0) THEN
          RETURN NULL;
        END IF;
        -- The result should equal the lowest number of significant numbers
        SELECT DECODE(math_operator, '*', NVL(firstnumber, 0) * NVL(secondnumber, 0), NVL(firstnumber, 0) / secondnumber)
          INTO math_result
        FROM DUAL;
        
        SELECT LEAST(LENGTH(REPLACE(REPLACE(TO_CHAR(NVL(firstnumber, 0)), ',', ''), '.', '')), LENGTH(REPLACE(REPLACE(TO_CHAR(NVL(secondnumber, 0)), ',', ''), '.', ''))),
          LENGTH(TO_CHAR(FLOOR(math_result)))
          INTO math_scale, math_length
        FROM DUAL;
        
        IF math_length >= math_scale
        THEN
          RETURN ROUND(math_result, 0);
        ELSE
          RETURN ROUND(math_result, math_scale - math_length);
        END IF;
      ELSIF math_operator = '+' OR math_operator = '-'
      THEN
        -- The result should be rounded to the number with lowest number of digits
        SELECT LEAST(LENGTH(TO_CHAR(MOD(NVL(firstnumber, 0), 1))) -1, LENGTH(TO_CHAR(MOD(NVL(secondnumber, 0), 1))) -1)
          INTO math_precision
        FROM DUAL;
        
        IF math_operator = '+'
        THEN
          RETURN ROUND(NVL(firstnumber, 0) + NVL(secondnumber, 0), math_precision);
        ELSE
          RETURN ROUND(NVL(firstnumber, 0) - NVL(secondnumber, 0), math_precision);
        END IF;
      END IF;
    END IF;
    RETURN NULL;
  END Calculate;

 

[SantaMufasa]

Ratjetoes,

Clever function ! It actually resembles the mental image I had for a function, as well. Does it work for you? What doesn't your function do that you want it to do?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[ratjetoes]

hi mufasa,

what the function does not correctly do is determine the total length of signifcant numbers when the last digit is a 0.

for example input 7.50. if you use NVL(7.50, 0) it returns 7.5. if you do TO_CHAR(7.50) it returns 7.5 therefor the LENGTH (after replace) returns 2 instead of 3. i can not give an inputmask to TO_CHAR because i don't know if the number has a last digit of 0. what i actually would want is that the TO_CHAR function would return the real input so it would return 7.50. for all other numbers it works well. one note though: NULL is being replaced with 0 which has a significance of 1. So 2.25 + 0 gives 2.

 

[SantaMufasa]

Ratjetoes,

First of all, it is an irreversible fact of the Oracle Universe that Oracle never stores leading or trailing zeroes for a number. In fact, if you pass 7.50 into a function as "function_x(7.50)", Oracle strips off the trailing 0 before the value even enters the function. Even if you define the function with a "<name> IN VARCHAR2" argument, Oracle recognizes that 7.50 = 7.5 and strips the trailing "0" before it passes the argument value to the function.

Now that we have those facts on the table, please tell me how you are storing the value "7.50".[ul][li]If the value is in a NUMBER column in an Oracle table, then there is absolutely, positively no trailing "0" in the data.[/li][li]If your data reside in a VARCHAR2 column, then how does the column receive its value of "7.50"? Are the data coming in from a non-Oracle data feed?[/li][/ul]

As soon as you let us know answers to my questions, above, I believe we can resolve your issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[ratjetoes]

hi mufasa,

the value is being stored in a NUMBER column so i guess it's not possible.

but i know now what caused the confusion: some of the values passed into the function already have been rounded in a view, therefor they could have a trailing zero.

tnx 4 the additional info though.

 

[jimirvine]

some of the values passed into the function already have been rounded in a view, therefor they could have a trailing zero.

Only If those values are now text i.e. there was most likely a to_char performed on the value within the definition of the view. If they are not text, then they do not have trailing zeros.

Jim

 

[SantaMufasa]

Ratjetoes,
[ul][li]IF it is vital to store/use numeric values that contain trailing zeroes to calculate significance, and[/li][li]IF you can cause your data containing the trailing zeroes to reside in a VARCHAR2 column[/li][/ul]...Then we can create a solution that does what you want. Please respond if the above conditions turn out to be true.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[ratjetoes]

hi jim, mufasa,

we discussed the issue in our team and the numbers have to be stored as NUMBER. the significance turns out not to be that important to the client (i.e. 7.50 is allowed to be calculated as 7.5). therefor we stick to the current solution.

many tnx to both of you for your responses though.