How to turn a number to zero if the result of a calculation is negative inside a squareroot in BigQuery?

I have a query that can result in a negative squareroot, leading to an error. I’m using BigQuery. Is there a way to turn the result in to a 0 if inside de POWER() function is less than 0?

Here’s my query:

UPDATE `table` SET 
SW_FERTL = CASE
WHEN POR_CLEAN < 0.04 THEN 1
WHEN (1/((rho_matrix-RHOB)/(rho_matrix-rho_mud_filt)))*POWER(((RW/ILD)+(POWER((0.3*VSHALE/2), 2)-(0.3*VSHALE/2))), 0.5) > 1 THEN 1
ELSE (1/((rho_matrix-RHOB)/(rho_matrix-rho_mud_filt)))*POWER(((RW/ILD)+(POWER((0.3*VSHALE/2), 2)-(0.3*VSHALE/2))), 0.5)
END
WHERE DEPTH_M IS NOT NULL

The negative squareroot can happen here:

POWER(((RW/ILD)+(POWER((0.3*VSHALE/2), 2)-(0.3*VSHALE/2))), 0.5)

So, if the result of

((RW/ILD)+(POWER((0.3*VSHALE/2), 2)-(0.3*VSHALE/2)))

is less than zero, I would like to set it to zero instead.

Is it possible to do this?

Thanks in advance!

Answer

You can use GREATEST():

GREATEST(0, ((RW/ILD)+(POWER((0.3*VSHALE/2), 2)-(0.3*VSHALE/2))))

Advertisement

Answer