Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Friday, 1 April 2016

SQL-NVL2

NVL2 Function we can pass three parameters if first parameter is not null it returns second parameter. if first parameter is null it return third parameter

Syntax:  NVL2 (String1, expr2 , expr3)

If String1 is not null expr2 will return
If String1 is null expr3 will return

Ex:
         SQL> select * from student;          -- here for 3rd row marks value is null

                         NO  NAME    MARKS
                          ---   -------       ---------
                          1        a           100
                          2        b           200
                          3        c

SQL> select no, name, nvl2(marks,300,100) from student;


                       NO NAME  NVL2(MARKS,300)
                       ---  -------  ---------------------
                        1           a              300
                        2           b              300
                         3          c              100


You May Like                                             
                            SQL Abs
                            SQL Sign
                            SQL Sqrt
                            SQL Mod
                            SQL NVL                           
                            SQL Power
                            SQL Exp
                            SQL Ln
                            SQL Log
                            SQL CEIL
                            SQL Floor
                            SQL Round
                            SQL Trunc
                            SQL Bitand
                            SQL Greatest
                            SQL Least
                            SQL Coalesce
Share this article :

2 comments:

  1. Hello There,

    Your blog is such a complete read. I like your approach with Oracle Apps Tutorials . Clearly, you wrote it to make learning a cake walk for me.

    I have two tables A and B. Where A is a parent table and B is a child table.
    I need all the records from table A that is not present in table B.

    I would like to know the best way in terms of performance to achieve this. (I have millions of records in the tables).

    But nice Article Mate! Great Information! Keep up the good work!

    Thank you,
    Preethi

    ReplyDelete
  2. Hi There,

    In debt to you for making my learning on the SQL-NVL2 area so hassle-free! I lay my faith on your writings.
    I am facing one ora-error while executing the procedure.
    ORA-06512: at "Lax.CLOSE_DUP"
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    i am suspecting it could be length issue with some of variables i have declared inside procedure
    l_effectreqcount number;----> This is tracking no of records being fetched.
    l_postdata varchar2(999); ---> This is the variable for which i believe the error has come.
    The reason is that in this variable l_postdata I am concatenating all the data being fetched from cursor.it is something like this.

    THANK YOU!! This saved my butt today, I’m immensely grateful.

    Cheers,
    Preethi

    ReplyDelete