Oracle SQL's Round and NVL-CodePudding

What is correct nvl before or after round?

Select nvl(round(10.5555,2),0) as value from dual

Select round(nvl(10.5555,0),2) as value from dual


Select nvl(round(COLUMN,2),0) as value from my_table

Select round(nvl(COLUMN,0),2) as value from my_table

Both query return some results, but what is best practice?

CodePudding user response：

It doesn't matter.

In this case, NVL isn't even used as 10.5555 is never NULL. If you used a parameter instead:

SQL> select nvl(round(&value, 2), 0) as value from dual;
Enter value for value: 10.5555

     VALUE
----------
     10,56

SQL> select round(nvl(&value, 0), 2) as value from dual;
Enter value for value: 10.5555

     VALUE
----------
     10,56

SQL>

As you can see, no difference.

CodePudding user response：

There is no "one" correct way for validating code. It always depends what you want to achieve. Both are correct and both return the same result. Only the logic behind it (and therefore, the reasoning) is different.

NVL(ROUND) will fallback if there is no value resulted from rounding. ROUND(NVL) will always round, but will consider 0 if there is no value.

In this case, you will need to know if the value inside ROUND() is guaranteed to be sent. If it isn't, and it's a bug, normally it should be fixed and it's not your problem to workaround it. If, for whatever reason, it cannot be fixed or is an optional value, there are several methods.

If you're using PL/SQL you can just do a IF NOT NULL and execute code only if the value exists, assuming it's a large block of code and the value is vital to its execution or you can use NVL (to be fair, I prefer COALESCE instead of NVL).

If you're using SQL, since the value can be NULL, it would make most sense to use SELECT ROUND(NVL). So the second value. But as stated, both are correct, but treat the problem differently. You will hear some argue that SELECT NVL(ROUND) makes more sense, because rounding is done on real values, not on zero, and NVL should only sanitize missing values. While it's true, it makes even less sense to ROUND(NULL), which is why I suggested the second approach.

I recommend also looking up COALESCE, NVL2 and NULLIF. For your current example, COALESCE would be "better" (notice the quotes) than NVL. Both do the same thing, but COALESCE is a bit better. When you work with a lot of records and it's used in a lot of columns, it can give a small boost, but it won't be huge. The difference is NVL checks the data type, so you can't use parameters of different types, while COALESCE will not, so it will fail. If you know you will either receive NULL or a correct data type (so you won't get a date instead of number) then COALESCE is better.

CodePudding user response：

As written, they are both going to result in the same output.

However, if the default value has greater precision than you are rounding to then there is a difference:

SELECT COALESCE(ROUND(column_name,2), 1.2345) as round_inside,
       ROUND(COALESCE(column_name, 1.2345),2) as round_outside
FROM   table_name;

Note: I use COALESCE as it is ANSI standard, but functionally it is the same as NVL.

Then the output for the sample data:

CREATE TABLE table_name (column_name) AS
SELECT 12.34567 FROM DUAL UNION ALL
SELECT NULL FROM DUAL;

Is:

ROUND_INSIDE	ROUND_OUTSIDE
12.35	12.35
1.2345	1.23

As you can see, when you use COALESCE/NVL and then wrap it in ROUND then whatever the value or the default are then they are rounded. If you ROUND the value and then use COALESCE/NVL then the default value will not be rounded.

If you always want at-most 2 decimal places in the output then use ROUND(COALESCE(column_name, default_value),2) but if you can guarantee that the default value will never have more precision than you are rounding to then which you use does not matter.

fiddle