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

GFrost — Wed, 19 Mar 2025 10:41:54 GMT

Hello everyone,

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 * from fx_date_new.fxdate

);

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.

Errorr: 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?

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

ggsmith — Thu, 20 Mar 2025 14:00:28 GMT

I think the issue is in your subquery. You shouldn't have the entire cte query in parentheses. Only the column from your CTE. Your FROM clause is inside your udf arguments. See if you can use the example below to fix the issue.

CREATE OR REPLACE FUNCTION my_udf(input_str1 STRING, input_str2 STRING, input_int INT)
RETURNS STRING
RETURN CONCAT('Processed: ', input_str1, ', ', input_str2, ', ', CAST(input_int AS STRING));

with t1 as (
select 1 as one, 'sample' as data1, 'example1' as data2
union all
select 2, 'example', 'example2'
union all
select 3, 'test', 'example3'
)

select
my_udf(data1, data2, one) AS processed_data
from t1;

topic Passing values from a CTE (Common Table Expression) to user-defined functions (UDF) in Spark SQL in Data Engineering

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

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