How to return 0 if null from UDF in Snowflake

Question

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;
$$
;

score 1 · Accepted Answer · answered Jan 19 '22 at 07:25

1

Returning 0:

SELECT nvl(rid,0) from tableA;

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

answered Jan 19 '22 at 07:25

NickW

You can use variables in SQL UDFs. – Greg Pavlik Jan 19 '22 at 20:04
@GregPavlik - you can use a variable within a SQL UDF but you can’t declare it within the function, as far as I'm aware. Happy to be proved wrong, though: https://docs.snowflake.com/en/developer-guide/udf/sql/udf-sql-scalar-functions.html#using-sql-variables-in-a-udf – NickW Jan 19 '22 at 20:35
Yes, I missed the "inside" part of the ask for variables. They have to be passed in as a function parameter. – Greg Pavlik Jan 19 '22 at 20:43

1 Answers1