Trying to create Javascript base UDTF in Snowflake by passing the external Arguments(Arg1,Arg2) into the control SELECT in the function

Question

Can you please help on below requirement in snowflake. We need to pass external argument into the query and need to build query based on external argument values then get out put of query in tabular format. Below is the javascript base UDTF we are trying to create but couldn't finish as we are new to javascript.

CREATE OR REPLACE FUNCTION GetV_Test(I_VendorName varchar(100),I_Department DOUBLE)

RETURNS TABLE(VendorName VARCHAR(100), Vendor VARCHAR(100)) LANGUAGE JAVASCRIPT

AS $$

if ((I_VendorName != null || I_VendorName !='') && (I_Department == null || I_Department == 0))
    {
        SELECT ' ' AS VendorName, ' ' AS Vendor
            UNION 
        SELECT distinct(Cast(Vendor as varchar(1000)) || '  ' || VendorName) as VendorName, Vendor 
        FROM vdrs WHERE VendorName LIKE '%'||I_VendorName||'%'
    }
else if((I_VendorName ==  null || I_VendorName == ' ') && (I_Department != null || I_Department!=0))
    {           
        SELECT ' ' AS VendorName, ' ' AS Vendor
            UNION 
        SELECT distinct (Cast(Vendor as varchar(1000)) || '  ' || VendorName) as VendorName,Vendor 
        FROM vdrs WHERE department =I_Department order by Vendor
    }   
else 
    {
        SELECT ' ' AS VendorName, ' ' AS Vendor
            UNION 
        Select distinct (CAST(vdrs2.Vendor as varchar(1000)) || '  ' || vdrs2.VendorName) as VendorName,vdrs2.Vendor 
            from (SELECT (Cast(Vendor as varchar(1000)) || '  ' || VendorName) as VendorName,Vendor 
        FROM vdrs WHERE department =I_Department)as temp1 
        Inner join vdrs2 on vdrs2.Vendor=temp1.Vendor 
        and vdrs2.VendorName LIKE '%'||I_VendorName||'%'
        order by Vendor
    }

$$ ;

Gokhan Atil Gokhan Atil · Accepted Answer · 2021-08-30T10:45:18

You can not combine SQL and JavaScript like you do:

if () {
  SQL statement
} else {
  SQL statement
}

It's not possible to read from another table(s) with JavaScript UDTF. Please check the samples:

https://docs.snowflake.com/en/developer-guide/udf/javascript/udf-javascript-tabular-functions.html

As a workaround:

You may create a JavaScript Stored Procedure, run these SQL statements to fill a transient or temp table, and then query this table with another SQL.
You may try to combine these SQLs and create a SQL UDTF:

https://docs.snowflake.com/en/developer-guide/udf/sql/udf-sql-tabular-functions.html

Trying to create Javascript base UDTF in Snowflake by passing the external Arguments(Arg1,Arg2) into the control SELECT in the function

1 Answers