Useful Oracle Functions

1) Lead / Lag
Lead function is an analytic function that lets you query
More than one row in a table at a time without having to
Join the table to itself.

It returns values from the next row / Previous Row Respectively in the table.

To return a value from a previous row, try using the lag function.

Example: Business Case.

Need to retrieve the Login date and Last Login date.

Select UserName, LogInDate,
lag (LoginDate,1) over (ORDER BY LoginDate desc) AS prev_Logindate
From UserLoginDetails;

2) Coalesce

coalesce function returns the first non-null expression in the list.
If all expressions evaluate to null, then the coalesce function will return null.

Business Case:

Table has Mobile Number, Land Line, and Office Phone

Three different columns, three fields can be with null values,
We need to retrieve the contact Number for that record,

Select Coalesce ( mobileNumber,Landline,OfficePhone) from Table.

It will return the first not null value from table.

3) NVL2

NVL2 function extends the functionality found in the NVL function.
It lets you substitutes a value when a null value is encountered as well as
When a non-null value is encountered.

Select NVL2( string1, value_if_NOT_null, value_if_null ) As Desc From
MyTable.

All above three functions supports from Oracle version 8i and Above.