In my (hql) query, I have the following where-clause:
LOWER( f_umlautfree2( replace(user.lastName, ' ', '')||replace(user.firstName, ' ', '') ) ) LIKE LOWER ('%'||( f_umlautfree2( replace(replace(:name, ',', ''), ' ', '') ) )||'%')
If I issued a query "real" sql, it is "translated" as follows:
lower(idmanlight.f_umlautfree2((replace(user0_.lastName||' '||'')||replace(user0_.firstName||' '||'')))) like lower(('%'||idmanlight.f_umlautfree2(replace(replace('%Croci%'||','||'')||' '||''))||'%'))
causes an error, for example: "No replace (text) function exists ..."
Why is everything ,(between functional brackets) replaced by ||?
This is the full hql request:
select
distinct user.dbId,
user.lastName,
user.firstName
from
User user LEFT join user.permissions
where
LOWER( f_umlautfree2( replace(user.lastName, ' ', '')||replace(user.firstName, ' ', '') ) ) LIKE LOWER ('%'||( f_umlautfree2( replace(replace(:name, ',', ''), ' ', '') ) )||'%')
and
(user.employee IS NOT NULL OR user.lecturer IS NOT NULL OR user.ethRelated IS NOT NULL OR user.pdbGuest IS NOT NULL)
and
user.valid = :valid
order by
user.lastName, user.firstName asc
and this is the full final sql query:
select
distinct user0_.dbId as col_0_0_,
user0_.lastName as col_1_0_,
user0_.firstName as col_2_0_
from
idmanlight.NETHZ_USER user0_
left outer join
idmanlight.NETHZ_USER_PERMISSION permission1_
on user0_.dbId=permission1_.NETHZ_USER_dbId
left outer join
idmanlight.PERMISSION permission2_
on permission1_.permissions_dbId=permission2_.dbId
where
(
lower(idmanlight.f_umlautfree2((replace(user0_.lastName||' '||'')||replace(user0_.firstName||' '||'')))) like lower(('%'||idmanlight.f_umlautfree2(replace(replace('%Croci%'||','||'')||' '||''))||'%'))
)
and (
user0_.employee is not null
or user0_.lecturer is not null
or user0_.ethRelated is not null
or user0_.pdbGuest is not null
)
and user0_.valid=?
order by
user0_.lastName,
user0_.firstName asc