I am trying to create a function in Snowflake that returns to me the rows matching a condition, but it only works when there is only one row matching the condition.
I am tried to create a function in Snowflake such as:
CREATE OR REPLACE FUNCTION test (field_value STRING)
RETURNS STRING
AS
$$
SELECT
value_x
FROM mapping_table_test
WHERE field_value = num1
$$;
We are assuming a very simple table, mapping_table_test, with a field called num1 and a field named value_x. The problem is that the function works if there is only a unique value in the num1 column. If there are more, I get this message: Single-row subquery returns more than one row.
Example: If there is only a value "10", the following calling to function works, but not if the value appears more than once in num1.
SELECT test(10);
I tried to change part of it so it returns a table instead of a string, but it does not work.
RETURNS TABLE(mapped_value STRING)
When trying to call the function this way, it simply tells me that it does not exists.
>Solution :
When using a tabular User Defined Function you must call the function within the FROM clause:
CREATE FUNCTION t()
RETURNS TABLE(msg VARCHAR)
AS
$$
SELECT 'Hello'
UNION
SELECT 'World'
$$;
SELECT msg
FROM TABLE(t())
ORDER BY msg;
MSG |
---|
Hello |
World |