I have the following HQL-Query:
from Paperboy as paperboy
where
replace(replace(paperboy._mobile, ' ', ''), '/', '') = :nr or replace(replace(paperboy._phone, ' ', ''), '/', '') = :nr or
:nr in elements(paperboy._additionalPhoneNumbers)
Now the problem is, that I also have to replace spaces and slashs in the _additionalPhoneNumbers. Is this possible via HQL?
I tried things like
from Paperboy as paperboy
where
replace(replace(paperboy._mobile, ' ', ''), '/', '') = :nr or replace(replace(paperboy._phone, ' ', ''), '/', '') = :nr or
:nr in elements(replace(replace(paperboy._additionalPhoneNumbers, ' ', ''), '/', ''))
but this throws an exception. Mapping of Paperboy is like this:
<class name="Paperboy"
table="tblPaperboy"
lazy="false">
<id name="_key"
column="Id"
access="field" >
<generator class="assigned"/>
</id>
<property name ="_businessId" column="BusinessId" access="field" />
<many-to-one name="_agency" class="Agency" access="field" column="AgencyKey" />
<property name ="_lastname" column="Lastname" access ="field" />
<property name ="_firstname" column="Firstname" access ="field" />
<property name ="_phone" column="Phone" access ="field" />
<property name ="_mobile" column="Mobile" access ="field" />
<bag name="_additionalPhoneNumbers"
access="field"
fetch="subselect"
lazy="false"
table="tblPaperboyAdditionalPhoneNumbers">
<key column="PaperboyId"/>
<element column="PhoneNumber"
type="string"/>
</bag>
</class>