How to use multiple columns join in JPQL in collection datasource query

Support
#1

Hi wanted to perform like this
[2:25 PM] Advait Joshi

[2:09 PM] Advait Joshi

[CDATA[select e from drrcp2$DrrCsheetResult e where e.appliedStatus='N' and
 (e.drrKey,e.baseItem,e.replItem) not in (select i.drrKey,i.baseItem,i.replItem from drrcp2$DrrProcessCsheet i where i.processRemarks is null) ]]

how do i achieve this?

#2

Hi,

According to JPA specification, “not in” operation can be applied only to single arguments.

You can try JPQL constructs:
such as left join with on condition
e.g. something like this:

from drrcp2$DrrCsheetResult e
left join drrcp2$DrrProcessCsheet i on e.drrKey = i.drrKey and e.baseItem = i.baseItem and e.replItem = i.replItem
where ...

Or rewrite the query to the native SQL. In this case you’ll need to write a middleware service that loads the data.