How to call a Snowflake UDF in a Select query

Question

I have created a UDF function to lookup a reference table

CREATE OR REPLACE function udf_rdm_lookup_func_test(
src_system VARCHAR,
src_code VARCHAR, 
src_value varchar)
returns array 
as
$$
SELECT DISTINCT DESC FROM rdm WHERE SOURCE_CODE = SRC_CODE AND SOURCE_VALUE = SRC_VALUE AND SOURCE_SYSTEM = SRC_SYSTEM
$$
;

i am able to call the UDF in a simple SELECT query

select udf_rdm_lookup_func_test('XYZ','LOBCode','001');

but i want to use this as a lookup in while reading data from another table

SELECT DISTINCT COMPANY_CODE,SRC_SYSTEM_ID,
udf_rdm_lookup_func_test(SRC_SYSTEM_ID,'LOBCode',COMPANY_CODE)[2] 
FROM ABC;

Upon running that i am getting an error

SQL Error [2031] [42601]: SQL compilation error:¶Unsupported subquery type cannot be evaluated

Want to know how to create a function that can be used in a SELECT statement while reading data from another table? Please help

Hi Niresh, suggest having a look at user defined table functions - these might do the trick: docs.snowflake.net/manuals/sql-reference/… Some good examples on that page and you call it with the "table(UDF-NAME())" syntax — Mike Donovan
thank you but i want to avoid that because there are 10+ attributes which i want to do the lookup for and i want to avoid doing a join for each of the lookup — Niresh Gopalakrishnan

Mike Walton Mike Walton · Accepted Answer · 2020-02-21T17:32:42

Your query is returning more than 1 value. The suggestion to leverage a table function would handle this, but if you want to return it as a single value array, then you actually have to create the SQL statement to return that. Try using an ARRAY_AGG() function in your SELECT.

https://docs.snowflake.net/manuals/sql-reference/functions/array_agg.html

How to call a Snowflake UDF in a Select query

1 Answers