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
