cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Correlated column is not allowed in non predicate in UDF SQL

Databricks143
New Contributor III

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 

 

 

4 REPLIES 4

Noopur_Nigam
Valued Contributor II

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

 I am running in notebook.

Noopur_Nigam
Valued Contributor II

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

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)

 

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group