[SOLVED] How to return 0 if null from UDF in Snowflake

Issue

I am writing user defined functions in Snowflake and doing some SELECT in the functions. I need to return 0 if select returns NULL.

In the below example if there is no rid available from tableA, function is returning NULL, but i need my function to return 0 instead of NULL.

Also, how to declare a variable inside function in snowflake?

Eg:

CREATE OR REPLACE FUNCTION A(ID int)
RETURNS float(53)
AS 
$$
         
        SELECT rid from tableA;
$$
;

Solution

Returning 0:

SELECT nvl(rid,0) from tableA;

To use variables you would need to write javascript UDFs rather than sql UDFs

Answered By – NickW

Answer Checked By – Katrina (BugsFixing Volunteer)