CODEWITHSUNDEEP
sqlfunctionnullargumentssnowflake-cloud-data-platform
daniel
daniel

Reputation: 761

Calling a snowflake function with NULL arguments

Trying to invoke a Snowflake table function with a NULL arguments but it looks to be imposible

PoC function I'm trying to invoke

CREATE OR REPLACE FUNCTION add5_colo (n1 number, n2 number, n3 VARCHAR) 
RETURNS table( i VARCHAR)
  AS
  $$
    SELECT 'n1 + n2 + 5 + n3' AS i
  $$
  ;

When I call the function as

SELECT * FROM table(add5_colo(1, 2, NULL));

I get the following error

SQL compilation error: error line 1 at position 20 Invalid argument types for function 'ADD5_COLO': (NUMBER(1,0), NUMBER(1,0), NULL)

We've spend all day reading documentation and coding different ideas but nothing have worked yet

Upvotes: 3

Views: 932

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

It is possible to pass NULL as argument to function call. It has to be casted first to match expected parameter's data type:

 SELECT * FROM table(add5_colo(1, 2, NULL)); 
 -- error

Should be:

SELECT * FROM table(add5_colo(1, 2, NULL::VARCHAR));

Upvotes: 3

NickW
NickW

Reputation: 9778

Try using ‘’ (empty string) instead of NULL for the VARCHAR parameter

BTW, i’m not sure what you want the function to do but, as written, it looks like it will return the string 'n1 + n2 + 5 + n3' every time, regardless of what your input values are

Upvotes: 0

Related Questions