I’m trying a natural log transformation on a field. For most values of the field this makes sense, but there are some values less than 0. In this case I’d like to either make null or replace with 0.

select ln(0)
Invalid floating point operation: log(e,0)

I found references to try_cast(), but this doesn’t seem appropriate for my need.

Is there a way I can try ln(field) and not error if snowflake encounters a value =< 0?

>Solution :

You can use the IFF function to do this.

set my_val = -1;

select iff($my_val <= 0, null, ln(abs($my_val)));

set my_val = 2;

select iff($my_val <= 0, null, ln(abs($my_val)));

Someone may notice the ln function has an abs around the value and spot a superfluous expression. This is for error safety. I’ve seen cases with some functions where both sides of a conditional or list of expressions will be evaluated. Even though one part will be thrown away by the IFF or COALESCE, etc, it’s safer to make sure math expressions won’t lead to an error even in what should be unreachable forks.