CODEWITHSUNDEEP
sql-serverfunctionsnowflake-cloud-data-platform
Akshay RS
Akshay RS

Reputation: 3

SnowFlake function doesn't return data and errors out

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

Upvotes: 0

Views: 479

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25938

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.

Upvotes: 1

Related Questions