SQL function does not work in 'Create Function'

Lily99 — Tue, 23 May 2023 17:28:32 GMT

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

Re: SQL function does not work in 'Create Function'

lucasrocha — Thu, 06 Jun 2024 22:53:51 GMT

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

topic Re: SQL function does not work in 'Create Function' in Data Engineering

SQL function does not work in 'Create Function'

Re: SQL function does not work in 'Create Function'