topic Re: Correlated column is not allowed in non predicate in UDF SQL in Data Engineering

Correlated column is not allowed in non predicate in UDF SQL

Databricks143 — Mon, 25 Sep 2023 17:28:02 GMT

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

Re: Correlated column is not allowed in non predicate in UDF SQL

Noopur_Nigam — Tue, 26 Sep 2023 10:51:49 GMT

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

Re: Correlated column is not allowed in non predicate in UDF SQL

Databricks143 — Tue, 26 Sep 2023 10:55:08 GMT

I am running in notebook.

Re: Correlated column is not allowed in non predicate in UDF SQL

Noopur_Nigam — Tue, 26 Sep 2023 10:58:50 GMT

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

Re: Correlated column is not allowed in non predicate in UDF SQL

Databricks143 — Wed, 27 Sep 2023 03:52:34 GMT

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)