Issue with return type “Table” in Snowflake

1
votes

I am finding a unique case in snowflake function where return type of my function is table.

  • Input parameters: INTEGER
  • Return parameters: TABLE

Problem statement: I am passing the parameter (one of the column) to function from a table and when there are only one row in the table, I am getting error

SQL compilation error: Unsupported subquery type cannot be evaluated

whereas it's working fine in cases of when number of records in table is more then 1.

My question: is there any pre-requisite for return type as table that number of records in source should be greater than 1 ?

1
database-designsnowflake-cloud-data-platform
In addition to that when I tried passing the input parameter value manually, the function is working fine. I am not sure what is the issue with function processing - BlackJack

1 Answers

2
votes

I would like to be able to add this as a comment, but was not able to because of reputation.

  • In order to better troubleshoot the query that you are receiving this error, can you please share the function?
  • What is the value type of the column you are passing and an a few examples of input?

In general Snowflake does corrections support subquery types and generally I recommend, or have seen recommendations to create a stored procedure or to rewrite the query to try achieve the same output. User Defined Table Functions: >https://docs.snowflake.net/manuals/sql-reference/udf-table-functions.html

Based on your question: Is there any pre-requisite for return type as table that number of records in source should be greater then 1 ? I don't think so, but let's look at an example, can you share the function?

What think you may be running into: https://community.snowflake.com/s/question/0D50Z00008mNInrSAG/unsupported-subquery-type