coalesce or case-when efficiency

[milts]

hi experts,

i'd like to check a variable if it is null or missing. if so, i'll assign the value 0 to another column, else the other column will just take it's value.

which between these two is more efficient?

case when var1 not is missing then var1 else 0 end as var2

OR

coalesce(var1,0) as var2

thanks,
milton

[Cynthia_sas]

Hi:
This might be one of those instances where "your mileage may vary" depending on the rest of your query and the size of the file(s) being accessed/created. Running some benchmarks might be in order.

A review of the doc might be in order too:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#/documentation/cdl/en...

I have to think there was -some- reason for this note in the doc:

Note: If your query contains a large number of COALESCE function calls, it might be more efficient to use a natural join instead. See Natural Joins.


cynthia

[milts]

Thank you very much for the input Cynthia.

Milton