How to join a function with a table and pass table columns as parameter to that function in snowflake

0
votes

How to join a function with a table and pass table columns as parameter to that function.

While using the common syntax like :

select * 
    from favorite_years y join table(favorite_colors(y.year)) c;

I am getting error :

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

1
functionjoinsnowflake-cloud-data-platformsnowflake-schema
we need more information to debug - does it work with a simple function? can you share the function? - Felipe Hoffa

1 Answers

0
votes

I was able to get this example of a table function to run properly:

create or replace function favorite_colors ( year number )
  returns table (color string)
as 
 'select color from colors where yearid = YEAR';

create or replace table favorite_years (year integer);
insert into favorite_years values (2020),(2021),(2022);
create or replace table colors (yearid integer, color string);
insert into colors values (2021,'red'),(2022,'orange'),(2020, 'blue');


select * from favorite_years y join table(favorite_colors(y.year)) c;