Coalesce as a replacement for NVL

If you are trying to keep your SQL or evan your HQL database agnostic, which is generally a good thing. One way to do this is to not use NVL but use the more generally available (although not as optimized) Coalesce function. 

Coalesce takes two arguments and processes them from left to right and then returns the first non null value it finds. For instance if you wrote

coalesce(null, "smurf")

you would get "smurf" as the return.

A really good place to use coalesce is in Date processing. Say I'm looking for a recipe that has a nullable live_date and a nullable experation_date to see if the recipe is currently active. I would write:

select * from recipe r where coalesce(r.live_date, sysdate) <= sysdate and coalesce(r.expiration_date, sysdate) >= sysdate

Essentially this is a between that is null safe.

Give coalesce a try and let me know what you think.