Passing values from a CTE (Common Table Expression) to user-defined functions (UDF) in Spark SQL / Databricks

Question

I'm trying to pass a value from a CTE to my function (UDF). Unfortunately, it's not working. Here is the first variant:

WITH fx_date_new AS (
   SELECT CASE
         WHEN '2025-01-01' > current_date() 
               THEN CAST(date_format(add_months(current_date(), -1), 'yyyyMM') AS INT)
               ELSE CAST(date_format(add_months('2025-01-01', -1), 'yyyyMM') AS INT)
         END AS fxdate
)

select calcccy (1000,
                     'USD',
                     'EUR',
                     select fxdate from fx_date_new
                    );

The second variant with INNER JOIN/CROSS APPLY:

WITH fx_date_new AS (
   SELECT CASE
         WHEN '2025-01-01' > current_date() 
               THEN CAST(date_format(add_months(current_date(), -1), 'yyyyMM') AS INT)
               ELSE CAST(date_format(add_months('2025-01-01', -1), 'yyyyMM') AS INT)
         END AS fxdate
)

SELECT 
    fx_date_new.fxdate,
    UDF.*
FROM 
    fx_date_new
INNER JOIN     -- or CROSS APPLY 
    gold.calcccy(
                    1000, 
                    'USD', 
                    'EUR',
                    fx_date_new.fxdate
                    ) AS UDF;

Neither of the two variants works. Error: A column, variable, or function parameter with name fx_date_new.fxdate cannot be resolved

Do you have any idea how I can solve this?

What does your udf function calcccy look like? '2025-01-01' will never be > current_date(). Or am I missing something? — Anupam Chand
– Anupam Chand, Commented Mar 19 at 23:34
if you remove the column name from CTE select list and use cross apply. something like this SELECT * FROM fx_date_new CROSS APPLY gold.calcccy( 1000, 'USD', 'EUR', fx_date_new.fxdate ) as UDF ORDER BY fx_date_new.fxdate ; Did u get any error? — SQL006
– SQL006, Commented Mar 20 at 19:41
WITH fx_date_new AS ( SELECT CASE WHEN '2025-01-01' > current_date() THEN CAST(date_format(add_months(current_date(), -1), 'yyyyMM') AS INT) ELSE CAST(date_format(add_months('2025-01-01', -1), 'yyyyMM') AS INT) END AS fxdate ) SELECT * FROM fx_date_new CROSS APPLY gold.calcccy(1000, 'USD', 'EUR', fx_date_new.fxdate) as UDF ORDER BY fx_date_new.fxdate; — GFrost
– GFrost, Commented Mar 21 at 8:49
ERROR: Syntax error at or near 'APPLY': missing 'JOIN'. SQLSTATE: 42601 line 11, pos 10 — GFrost
– GFrost, Commented Mar 21 at 8:49

SQL006 · Accepted Answer · 2025-03-21 11:01:58Z

0

Please check this, cross apply is not supported in databricks sql

WITH fx_date_new AS ( SELECT CASE WHEN '2025-01-01' > current_date() THEN CAST(date_format(add_months(current_date(), -1), 'yyyyMM') AS INT) 
ELSE CAST(date_format(add_months('2025-01-01', -1), 'yyyyMM') AS INT) 
END AS fxdate ) 
SELECT * FROM fx_date_new 
JOIN (SELECT gold.calcccy(1000, 'USD', 'EUR', fx_date_new.fxdate) as calculatedfunctionvalue) as UDF

answered Mar 21 at 11:01

SQL006

5678 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GFrost Mar 24 at 8:42

ERROR: [UNRESOLVED_COLUMN.WITHOUT_SUGGESTION] A column, variable, or function parameter with name fx_date_new.fxdate cannot be resolved.

SQL006 Mar 24 at 9:54

can you share your function definition?

GFrost Mar 25 at 10:35

(SELECT calcCCY.amount / (( select first(rate) from gold.ccy_mnth WHERE ccy_mnth.CCY_ID = calcCCY.currencyFrom AND ccy_mnth.LGL_MNTH_CODE = calcCCY.fxDate AND to_date(CCY_ACTIV_VT) = '9999-12-30' ) / ( select first(rate) from gold.ccy_mnth WHERE ccy_mnth.CCY_ID = calcCCY.currencyInto AND ccy_mnth.LGL_MNTH_CODE = calcCCY.fxDate AND to_date(CCY_ACTIV_VT) = '9999-12-30' )))

Collectives™ on Stack Overflow

Passing values from a CTE (Common Table Expression) to user-defined functions (UDF) in Spark SQL / Databricks

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related