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

Leave a Reply

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


six − = 1