Correlated column is not allowed in non predicate in UDF SQL

Databricks143
New Contributor III

‎09-25-2023 10:23 AM - edited ‎09-25-2023 10:28 AM

Hi Team,

I am new to databricks and currently working on creating sql udf 's  in databricks .

In udf we are calculating min date and that date column using in where clause also.

While running udf getting  Correlated column is not allowed in  non predicate 

Ex: In Udf we are calling as below 

Select min(hiredate) from Emp where empno=&1  and hiredate<=&2

&1 and &2 are function input  parameters.

Request you please help me on this 

 

 

0 Kudos

Noopur_Nigam
Databricks Employee
Databricks Employee

‎09-26-2023 03:51 AM

Hi @Databricks143 Are you running the query in DBSQL or in notebook?

0 Kudos

Databricks143
New Contributor III
In response to Noopur_Nigam

‎09-26-2023 03:55 AM

 I am running in notebook.

0 Kudos

Noopur_Nigam
Databricks Employee
Databricks Employee

‎09-26-2023 03:58 AM

Could you please provide your full code? I would also like to know which DBR version you are using in your cluster.

0 Kudos

Databricks143
New Contributor III
In response to Noopur_Nigam

‎09-26-2023 08:52 PM

Please find below query and DBR version that we are using in cluster

%sql

create or replace function first_con_date(
indicator String,
startingdate Date,
transingdate Date,
status String,
entid String,
bookingid String,
ftype String,
transfertype String)
Returns
Date
return
case when indicator ='Y' then
(select min(Trans_date)
from transfer
where statusflag=status
and Transid=entid
and methodid=bookingid
and fintype=ftype
and trans_type=transfertype
and trans_date<=transingdate)
else
add_months(startingdate,-1)

DBR Version:- 9.1LTS(includes Apache Spark 3.1.2,Scala 2.12)

 

0 Kudos