This SQL statement works fine by itself

SELECT COUNT(1)

FROM tablea f

INNER JOIN tableb t ON lower(f.col1) = t.col1

but if I want to use it inside a function:

​

CREATE OR REPLACE FUNCTION fn_abc(var1 STRING, var2 STRING)

RETURNS DOUBLE

COMMENT 'test function'

RETURN SELECT

    CASE

    WHEN EXISTS(

        SELECT COUNT(1)

        FROM tablea f INNER JOIN tableb t ON lower(f.col1) = t.col1)

    THEN 10.5

    END;

​

I got an error:

AnalysisException: [UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.CORRELATED_COLUMN_IS_NOT_ALLOWED_IN_PREDICATE] Unsupported subquery expression: Correlated column is not allowed in predicate: (lower(Col1#54978) = lower(outer(col1#54938)))

​

can someone give me an idea on why this is happening and how to work around it? thanks