COALESCE fn will return the first NOT-NULL value from the list starting from left to right. Here order of checking is important.
This will solve many if-then-else conditions in a single function.
For example:
In a query where a column value is checked against 3 different subqueries, then coalesce fn can be used.
Select * from table_A
where A.tdate = COALESCE (
( select somedate from Table_B where ....),
( Select somedate from Table_C where ...) ,
( select somedate from Table_D where ...)
) ;
Here, if the first sub query ( from Table_B) returns NULL, then the 2nd Query will be executed. If the 2nd query ( From Table_C) returns null , then the 3rd Query ( from Table_D) will be executed.
Hope this help you.
Tamil
