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?

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?

• apache-spark-sql
• databricks
• user-defined-functions
• common-table-expression
• microstrategy

• What does your udf function calcccy look like? '2025-01-01' will never be > current_date(). Or am I missing something? – 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 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 Commented Mar 21 at 8:49
• ERROR: Syntax error at or near 'APPLY': missing 'JOIN'. SQLSTATE: 42601 line 11, pos 10 – GFrost Commented Mar 21 at 8:49

1 Answer 1

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