Unable to Concat columns in JPQL queries

Support
#1

Hi,

detailsDl.setQuery(" SELECT  e FROM details e JOIN user u  ON e.createdById = u.id where concat(e.createdById, '--', u.Id) in '122154--1' order BY e.createdOn DESC ");
        detailsDl.load();

I am using below jpql query to fetch data but getting CommonErrorNode [<unexpected: [@85,215:215=‘e’,<61>,1:215], resync=concat(e.createdById, ‘–’, u.Id) in

SELECT  e FROM details e  
JOIN user u 
ON e.createdById = u.id 
where concat(e.createdById, '--', u.Id) in '122154--1' order BY e.createdOn DESC

Thank you in advance.

#2

Replace ‘in’ to ‘=’
concat(e.createdById, ‘–’, u.Id) = ‘122154–1’

#3

Values are multiple so it would required in
concat(e.createdById, ‘–’, u.Id) in (‘122154–1’,‘122153–2’)

#4

e.createdById is Integer type. Try cast to text - CAST(e.createdById text) JPQL Extensions :: Jmix Documentation

#5

Still getting same error

#6

u.Id cast too.

#7

Hi,

Sorry for incomplete reply

I had cast both the column as per your previous reply but still getting below error.

AND CONCAT( CAST(e.createdById text), '--', CAST(u.Id text) ) in :UsersWithRole

CommonErrorNode [<unexpected: [@93,223:227='CAST(',<89>,1:223], resync=CONCAT( CAST(e.createdById text), '--', CAST(u.Id text) ) in :UsersWithRole>]