SQL function does not work in 'Create Function'
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-23-2023 10:28 AM
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
- Labels:
-
SQL
-
SQL Statement
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-06-2024 03:53 PM
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
![](/skins/images/582998B45490C7019731A5B3A872C751/responsive_peak/images/icon_anonymous_message.png)
![](/skins/images/582998B45490C7019731A5B3A872C751/responsive_peak/images/icon_anonymous_message.png)