SnowFlake function doesn't return data and errors out

Question

I have a function which accepts 3 varchar inputs and returns 1 varchar output.

The function works quite well in SQL Server but not in Snowflake. I don't see what's wrong. Everything seems to be fine.

Note: it works fine when a string is passed but not when column is passed. I am not sure why it isn't accepting multiple rows??

SELECT Get_Status_Descriptiontest('can', '1', '2')

Below is the query:

with cte as (
    select 'CAN' as a, 1 as b
    union
    select 'Ban' as a, 2 as b
)
SELECT Get_Status_Descriptiontest(a, '1', '2'), *
FROM cte;

Gives the error

SQL compliation error: Unsupported subquery type cannot be evaluated

This is the example function:

create or replace function Get_Status_Descriptiontest(Status_Code1 varchar, Status_Code2 varchar , Status_Code3 varchar )
    returns varchar 
    as--to get sume of workdays in a given date range
$$
    WITH cte AS ( 
        SELECT 'can' DRKY, '1' DRSY, '2' DRRT, 'output1' DRDL01
    )
    SELECT TRIM(DRDL01) 
    FROM    cte
    WHERE TRIM(DRKY)= TRIM(Status_Code1) and TRIM(DRSY) = TRIM(Status_Code2) AND TRIM(DRRT) = TRIM(Status_Code3) 
$$
;

with the real function looking like:

create or replace function Get_Status_Description(Status_Code1 varchar, Status_Code2 varchar , Status_Code3 varchar ,brand varchar) 
    returns varchar as 
$$ 
    SELECT TRIM(DRDL01) 
    FROM DB.Schema.F0005
    WHERE TRIM(DRKY)= TRIM(Status_Code1) and TRIM(DRSY) = TRIM(Status_Code2)
       AND TRIM(DRRT) = TRIM(Status_Code3) AND brand='TPH' 
$$;

Dale K · Accepted Answer · 2020-05-06 06:15:40Z

1

The error message is telling you are doing a correlated sub-query which snowflake does not support. Which SQL Server does support.

Now your question has been edited so the context is more visible I can see the problem is noted as above.

Firstly even if this worked you will get no results as your case comparing 'can' to 'Can' and in snowflake strings are compared case sensitive.

Secondly your data CTE can be rewritten as

WITH cte AS (
     SELECT * FROM VALUES ('CAN', 1), ('Ban', 2)  v(a, b)
)

which makes adding more rows simpler.

A general rule is not to call functions on the FILTERS, thus the TRIM's should be done in your CTEs:

WITH cte AS (
     SELECT TRIM(a) AS a, b FROM VALUES ('CAN', 1), ('Ban', 2)  v(a, b)
)

and

WITH cte AS ( 
    SELECT TRIM(column1) AS drky
        ,TRIM(column2) AS drsy
        ,TRIM(column3) AS drrt
        ,TRIM(column4) AS drdl01
    FROM VALUES ('can', '1', '2', 'output1');
)

and thus this would work if we ignore there is no matching data:

WITH cte AS (
     SELECT TRIM(a) AS a, b FROM VALUES ('CAN', 1), ('Ban', 2)  v(a, b)
), Status_Descriptiontest AS ( 
    SELECT TRIM(column1) AS drky
        ,TRIM(column2) AS drsy
        ,TRIM(column3) AS drrt
        ,TRIM(column4) AS drdl01
    FROM VALUES ('can', '1', '2', 'output1')
)
SELECT sd.drdl01 as  description,
    c.*
FROM cte AS c
JOIN Status_Descriptiontest AS sd
    ON sd.DRKY = c.a and sd.DRSY = '1' AND sd.DRRT = '2';

so going to ILIKE to have a exact match without case sensitivity:

WITH cte AS (
     SELECT TRIM(a) AS a, b FROM VALUES ('CAN', 1), ('Ban', 2)  v(a, b)
), Status_Descriptiontest AS ( 
    SELECT TRIM(column1) AS drky
        ,TRIM(column2) AS drsy
        ,TRIM(column3) AS drrt
        ,TRIM(column4) AS drdl01
    FROM VALUES ('can', '1', '2', 'output1')
)
SELECT sd.drdl01 as  description,
    c.*
FROM cte AS c
JOIN Status_Descriptiontest AS sd
    ON sd.DRKY ilike c.a and sd.DRSY ilike '1' AND sd.DRRT ilike '2';

gives:

DESCRIPTION    A    B
output1        CAN  1

In effect anything you are wanting to do in the SQL function, you can do in a CTE, so you might as well do that.

edited May 6, 2020 at 6:15

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

answered May 6, 2020 at 3:27

Simeon Pilgrim

26.7k3 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Akshay RS Over a year ago

Hi Simeon, Thanks for answering. I actually use tables inside function and in the query itself. I have used CTE here so i can simulate the table and better explain my situation. Its a simple thing. I am just passing 3 string(status codes) values and the function is supposed to look it up from a table and return another string(description). (I know i can simply do a join and get it but i really want to use it as a function as we use it in lot of places) Below comment has the actual function.

Akshay RS Over a year ago

Below is the actual function.

create or replace function Get_Status_Description(Status_Code1 varchar, Status_Code2 varchar , Status_Code3 varchar ,brand varchar)   returns varchar    as   $$  	SELECT TRIM(DRDL01) FROM  	DB.Schema.F0005  	WHERE TRIM(DRKY)= TRIM(Status_Code1) and TRIM(DRSY) = TRIM(Status_Code2) AND TRIM(DRRT) = TRIM(Status_Code3) AND brand='TPH'   $$   ;

And the query is a simple: select Get_Status_Description(column1,'44H5', 'ST', 'TPH') from table1

Simeon Pilgrim Over a year ago

nod, I guess to to 'example-ness' of the code. Snowflake still re-writes the code as a correlated sub query, and then fails to run it. The function seems like a free lunch, but it's one for now, snowflake will not let you eat... It's been like this since they introduced functions. Open a support case to register you displeasure, but in the mean time, you will need to rewrite the SQL to get it to run.

Collectives™ on Stack Overflow

SnowFlake function doesn't return data and errors out

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related