SQL function does not work in 'Create Function'

[Lily99]

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

[lucasrocha]

Hello @Lily99 , I hope this message finds you well.

Could you please try the code below and let me know the results?

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 WHERE lower(f.col1) = t.col1)
    THEN 10.5
    END;

Best regards,
Lucas Rocha