|Oracle to Microsoft SQL Server|
Scenario: I had this scenario where I am supposed to show an email address of an insurance dependent, if this dependent email id is not updated then we should fetch the email id of the parent. If in Oracle I would have gone for the NVL function or a CASE statement to achieve the same. If it just comparing maybe two to three columns then a CASE statement would suffice. What if we had more than 10 columns in a way that we have to show data if one of these 10 columns are having data.
Solution: In this scenario I could go for the COALESCE function in Microsoft SQL Server as the it doesn't provide the NVL function and anyways the COALESCE function performs better than NVL in Oracle and makes your code simpler.
eg: Imagine we have a CUSTOMER table with 4 email address columns, EMAIL_1, to EMAIL_10. we have to return the first available data among these columns checking serially. If in Oracle we could have gone for the below syntax,
SELECT NVL(EMAIL_1,NVL(EMAIL_2,(NVL(EMAIL_3, NVL(EMAIL_4,'Not available,),,'Not available,),'Not available,),'Not available,) EmailId
As you can see the code is messy and may take time to understand. But SQL Server provides you a function for such a scenario called the COALESCE function, which does the same for you. The above result in COALESCE would look like below,
SELECT COALESCE(EMAIL_1,EMAIL_2,EMAIL_3,EMAIL_4) EmailId FROM CUSTOMER.
Use of the function makes way for simpler and clean code. The COALESCE function converts your code to a CASE function and executes the same. The difference with ISNULL function is that the ISNULL function is executed only once where as COALESCE is executed until a non NULL argument is reached.
Hope this came of help to you. Happy coding.