ORACLE NULL VALUE SUM CASE

Hi

In this entry  , I will explain summation problem about NULL values.

Case:

Environment: Oracle 10g


SELECT  NUM1+NUM2+NUM3  AS TOTAL
FROM
(
        SELECT  1  AS NUM1  ,  5  AS NUM2   , NULL AS NUM3
        FROM
        DUAL
)

Here query  that i wrote above  result  is  NULL  ( NULL value  of NUM3 causing result error )

True form of this query as follows:

SELECT  NUM1+NUM2+NUM3  AS TOTAL

FROM

(

        SELECT  1  AS NUM1  ,  5  AS NUM2   ,  NVL(NULL, 0 ) AS NUM3

        FROM

        DUAL

)

In here result is  6...

Basically when doing arithmetic operation , it is quite important to put  NVL check over columns.

 ( If we are not sure about  data value within the columns )