Let Us Learn Oracle - Part 13 of N [ NVL2 function in Oracle ]

Source table: tblPlayers

Purpose: It is an extended version of NVL. It substitute a value when a null value is encountered as well as a non null value is encountered.

Syntax: NVL2( ColumnName, ReplaceValue_If_Not_Null,ReplaceValue_If_Null )

For the sake of illustration we have inserted one record where the Fee PerMatch is null.

Insert into tblplayers(PLAYERID,PLAYERFIRSTNAME,PLAYERLASTNAME,BELONGSTO,DOB,FEEPERMATCH)  Values(11,'P','Chopra','India',To_Date('14/02/1998','DD/MM/YYYY'),Null);

Now run the query

SQL> Select  PLAYERID,Concat(PLAYERFIRSTNAME,Concat(' ' , PLAYERLASTNAME)) PlayerName,NVL2(FeePerMatch,FeePerMatch,600) From tblplayers Order By PLAYERID;

  PLAYERID PLAYERNAME            NVL2(FEEPERMATCH,FEEPERMATCH,600)
---------- --------------------- ---------------------------------
         1 A Raman                                            2000
         2 B Kadir                                            1000
         3 C Nadir                                            3000
         4 D Das                                               500
         5 E Punchkar                                        12000
         6 F Fateman                                         22000
         7 G Gajani                                            999
         8 H Hariharan                                        7000
         9 I Ikat                                             5999
        10 J Jwar                                             7900
        11 P Chopra                                            600

11 rows selected.

We can make out that for PlayerId 11, the value for FeePerMatch has been replaced by 600.But for others it is 600

In Sql Server, we implement this functionality by using Coalesce.

Select 
	PLAYERID
	,Concat(PLAYERFIRSTNAME,Concat(' ' , PLAYERLASTNAME)) PlayerName	 
	,Coalesce(FeePerMatch,FeePerMatch,600)
From tblplayers Order By PLAYERID;

Thanks for reading.