Correlated column is not allowed in non predicate in UDF SQL

[Databricks143]

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 

[Noopur_Nigam]

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

[Databricks143]

 I am running in notebook.

[Noopur_Nigam]

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

[Databricks143]

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)