Understand Oracle NVL2 Function With Industry Scenarios !!

What is Oracle NVL2  function ( NVL as well ) ?

NVL, NVL2 are the most powerful Oracle function. They allow you to handle / treat / replace NULL values in data.

Where do you use it?

  1. Data can never be accurate. You can ensure quality checks before it pass through an Integration and transformation process.
  2. It is very common that your source system pass Null values to you. Sometime it is predictable and many of times unpredictable.
  3. If we want to go with different approaches, Just to manage null and non-null data values in our ETL process.
  4. These functions will help us too effectively to manage above situations.

 

Oracle NVL Function Syntax

Syntax for Oracle NVL2 function and  Oracle NVL function

If Expression (1) is null, nvl function returns Expression (2) as value.

If Expression (1) is not null, NVL function returns Expression (1) as a value.

Oracle NVL2 Function Syntax

Oracle NVL2 function syntax

If Expression (1) is null, NVL2 function returns Expression (3) as a value.

If Expression (1) is not null – NVL2 function returns Expression (2) as a value.

Oracle NVL2 Function ( NVL

) Some Examples –

Please see below example which is to calculate annual salary of employees. This showcase how arithmetic operations on the numeric data with null values lead to incorrect calculation of the annual salary. This is being done without oracle NVL function.

SELECT FIRST_NM, LAST_NM, SAL, COMMISSION,
(SAL+COMMISSION)*12 “ANNUAL SALARY”
FROM W_EMPLOYEE_D, W_DEPARTMENT_D
WHERE W_EMPLOYEE_D.DEPARTMENT_WID = W_DEPARTMENT_D.ROW_WID
AND W_DEPARTMENT_D.DEPT_NAME = ‘SALES';

FIRST_NMLAST_NMSALARYCOMMISSIONANNUAL SALARY
JasonB160030022800
JoannMuller125050021000
JonasSalk1250140031800
ForbesB.C2850(null)(null)
 SarahJones1500018000
 RamnathP950(null)(null)

2. To achieve same thing with oracle NVL function. In below SQL query calculation of annual salary of employees is being done. It showcase the usage of NVL function. Here we achieve the accurate calculation of annual salary.

SELECT FIRST_NM, LAST_NM, SAL, COMMISSION,
(SAL+NVL(COMMISSION,0))*12 “ANNUAL SALARY”
FROM W_EMPLOYEE_D, W_DEPARTMENT_D
WHERE W_EMPLOYEE_D.DEPARTMENT_WID = W_DEPARTMENT_D.ROW_WID
AND W_DEPARTMENT_D.DEPT_NAME = ‘SALES';

FIRST_NMLAST_NMSALARYCOMMISSIONANNUAL SALARY
JasonB160030022800
JoannMuller125050021000
JonasSalk1250140031800
ForbesB.C2850 NULL34200
 SarahJones1500018000
 RamnathP950 NULL11400

 

3. Now with Oracle NVL2 Function. See below SQL is again to calculate annual salary of employees. Here we are highlighting the use of NVL2 function. Take it for a particular year. The executive board decided to pay a flat commission of $100 pm extra to every employee of the sales department. Who are entitled to receive Commission as per their pay packages. In this case, the annual salary can be calculated as below.

SELECT FIRST_NM, LAST_NM, SAL, COMMISSION,
(SAL+NVL2(COMMISSION,COMMISSION+100,0))*12 “ANNUAL SALARY”
FROM W_EMPLOYEE_D, W_DEPARTMENT_D
WHERE W_EMPLOYEE_D.DEPARTMENT_WID = W_DEPARTMENT_D.ROW_WID
AND W_DEPARTMENT_D.DEPT_NAME = ‘SALES';

FIRST_NMLAST_NMSALARYCOMMISSIONANNUAL SALARY
JasonB160030024000
JoannMuller125050022200
JonasSalk1250140033000
ForbesB.C2850 NULL34200
SarahJones1500019200
RamnathP950 NULL11400

Hope it was helpful ,,,,,, looking forward to see your inputs in comments.


 

Comments

  1. Naresh Reddy says

    Hi Uma,

    I have a doubt in informatica my doubt is
    i have a 2 sessions in my workflow now my requirement is
    now i want to run my second session whenever first record insert into first session target table.

    How can i achieve this in informatica

    Regards
    Naresh Reddy

  2. says

    Interesting, I would love to know the scenario that needed this solution and also the first session data volume. Target volume is a key factor in this design. you may send the details to me later.I can think of below solution, but can customize it as required

    # First session / mapping –
    1. filter, file target. Only first record is to be written on file target (say file name is session1_first.completed
    2. *Use Sequence generator if you dont have a sequence, and configure it to cycle after every session (this point is Optional)

    # Second Session / mapping –
    1. Create a event wait before session
    2. Start session when session1_first.completed file size is > 0

    Please note that these two sessions are to be coupled paralelly in your workflow.

  3. Naresh Reddy says

    Hi Uma,

    Thanks for your replay i have two more doubts
    1. can i implement SCD TYPE2 logic without using look up and update strategy transformations if it possible please how can i achieve this

    (OR)
    can i compare source data with target data with out using look up transformation.

    i have 2 workflows w1 and w2
    W1–>s1—–s2
    W2–>s3–>s4
    First I want to execute s1 after that s3 next s2 after that s4 please explain clearly

    HOW CAN I ACHIEVE THIS USING INFORMATICA
    3.I have 10 flat file as a source i want to load 10 files in 10 targets in a single mapping flow (without using 10 pipe lines .

    Regards
    Naresh Reddy

    • Budha says

      One solution of the third scenario
      If you know the source file names
      1.Enable “Add currently processed file name “ property, it will pass the file name to the next transformation.
      2.Use router transformation to identify the file name and connect the pipes to the appropriate target. ( I hope you know the router transformation)
      3.Select “Source filetype as Indirect” in the session properties. ( I hope you know how to use INDIRECT filetype)

    • Budha says

      One of the Solution for the second scenario
      W1=S1-> cmd task(to create a file F1)–>Event task(to wait until file F2 arrive)–>S2–> command task (to create a file F3)
      W2=Event task( to wait until file F1 arrive)–>S–>command task (to create a file F2)–> Event task( to wait until file F3 arrive)–>S4

  4. smart says

    This article will provide you a summary of the NULL functions available for handling null values in Oracle.

    All examples are described with the use of following table.

    CREATE TABLE TEST_NULL_VALUE (
    test_id NUMBER(4),
    value1 VARCHAR2(12),
    value2 VARCHAR2(12),
    value3 VARCHAR2(12),
    value4 VARCHAR2(12),
    value5 VARCHAR2(12)
    );

    INSERT INTO TEST_NULL_VALUE values (100, ‘FIRST’,’SECOND’,’THIRD’,’FORTH’,’FIFTH’);
    INSERT INTO TEST_NULL_VALUE values (101, NULL,’SECOND’,’THIRD’,’FORTH’,’FIFTH’);
    INSERT INTO TEST_NULL_VALUE values (102, NULL,NULL, ‘THIRD’, ‘FORTH’,’FIFTH’);
    INSERT INTO TEST_NULL_VALUE values (103, NULL, NULL, ‘THIRD’, ‘FORTH’,’FIFTH’);
    INSERT INTO TEST_NULL_VALUE values (104, NULL, NULL, NULL, ‘FORTH’,’FIFTH’);
    COMMIT;
    When we select TEST_NULL_VALUE table data we get the following result. Now we will use NULL functions to check the result of those functions.

    SELECT * FROM TEST_NULL_VALUE ORDER BY TEST_ID;

    TEST_ID VALUE1 VALUE2 VALUE3 VALUE4 VALUE5
    ———- ———— ———— ———— ———— ————
    100 FIRST SECOND THIRD FORTH FIFTH
    101 SECOND THIRD FORTH FIFTH
    102 THIRD FORTH FIFTH
    103 THIRD FORTH FIFTH
    104 FORTH FIFTH

    5 rows selected.

    SQL>
    NVL Function:

    The NVL function permits you to replace all null values with a default value. It requires two parameter values first value is column name and second is any default value.
    If the value in the first parameter is null then the function returns the value in the second parameter. If the first parameter is any value other than null, it returns unchanged value.
    Here we are using the NVL function to replace the null values with ‘ZERO’.

    SELECT TEST_ID, NVL(value1, ‘ZERO’) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

    TEST_ID RESULT
    ———- ————
    100 FIRST
    101 ZERO
    102 ZERO
    103 ZERO
    104 ZERO

    5 rows selected.

    SQL>
    Now you can see the above example where null value has been replaced with ZERO.

    DECODE Function:

    The DECODE function is not specifically designed for handling null values but it can be used to replace NULL values just like NVL function.

    SELECT TEST_ID, DECODE(value1, NULL, ‘ZERO’, value1) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

    TEST_ID RESULT
    ———- ————
    100 FIRST
    101 ZERO
    102 ZERO
    103 ZERO
    104 ZERO

    5 rows selected.

    SQL>
    NVL2 Function:

    The NVL2 function is entirely different from NVL function because it accepts three parameters.
    If the first column value is not null then it returns the value of the second column. If the first column value is null then it returns the value of third column.

    SELECT TEST_ID, NVL2(value1, value2, value3) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

    TEST_ID RESULT
    ———- ————
    100 SECOND
    101 THIRD
    102 THIRD
    103 THIRD
    104

    5 rows selected.

    SQL>

    SELECT TEST_ID, NVL2(value2, value3, value4) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

    TEST_ID RESULT
    ———- ————
    100 THIRD
    101 THIRD
    102 FORTH
    103 FORTH
    104 FORTH

    5 rows selected.

    SQL>
    In both the above examples columns are changed and you can see the results.

    COALESCE Function:

    The COALESCE function accepts two or more parameters and returns the first non null value in a list. If all parameters contain null values then it returns null.

    SELECT TEST_ID, COALESCE(value1, value2, value3) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

    TEST_ID RESULT
    ———- ————
    100 FIRST
    101 SECOND
    102 THIRD
    103 THIRD
    104

    5 rows selected.

    SQL>

    SELECT TEST_ID, COALESCE(value2, value3, value4) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

    TEST_ID RESULT
    ———- ————
    100 SECOND
    101 SECOND
    102 THIRD
    103 THIRD
    104 FORTH

    5 rows selected.

    SQL>

    SELECT TEST_ID, COALESCE(value1, value2, value3, value4) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

    TEST_ID RESULT
    ———- ————
    100 FIRST
    101 SECOND
    102 THIRD
    103 THIRD
    104 FORTH

    5 rows selected.

    SQL>
    NOTE:

    When you compare against null always, you always get result in null because NULL can’t be used with regular comparison operators like “=” or “!=”.

    SELECT * FROM TEST_NULL_VALUE WHERE VALUE1 = NULL ORDER BY TEST_ID;

    no rows selected

    SQL>
    Instead of you must use IS NULL or IS NOT NULL operators.

    SELECT * FROM TEST_NULL_VALUE WHERE VALUE1 IS NULL ORDER BY TEST_ID;

    TEST_ID VALUE1 VALUE2 VALUE3 VALUE4 VALUE5
    ———- ———— ———— ———— ———— ————
    101 SECOND THIRD FORTH FIFTH
    102 THIRD FORTH FIFTH
    103 THIRD FORTH FIFTH
    104 FORTH FIFTH

    4 rows selected.

    SQL>

Leave a Reply

Your email address will not be published. Required fields are marked *


six + = 9