Snowflake Unsupported subquery type cannot be evaluated in UDF

Question

I am trying to create a function that will take an h3id as input and returns the zip5 which the h3id belongs to, In order to do so I have created this UDF:

CREATE  FUNCTION get_zip_from_h3(h3_id STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
with point_table as (
SELECT h3_id as h3_cell_id,split_part(replace(replace(replace(st_astext(H3_CELL_TO_POINT(h3_id)),'POINT',''),')',''),'(',''),' ',1) as lon,
split_part(replace(replace(replace(st_astext(H3_CELL_TO_POINT(h3_id)),'POINT',''),')',''),'(',''),' ',-1) as lat)
select max(a.zip5) from ZIP5_polygon_table a,point_table b where
ST_WITHIN(ST_POINT(b.LON,b.LAT),TRY_TO_GEOGRAPHY(a.wkt))
$$;

The following example works properly

select get_zip_from_h3('8a275d02500ffff')

This returns exactly one column and one value.

However, when using the query in a normal select statement such as

SELECT get_zip_from_h3(H3_R10) AS ZIP FROM H3_sampletable LIMIT 500;

I do get the following error: Unsupported subquery type cannot be evaluated

Any idea on how to resolve this?