Hi,

I am trying to concatenate some attributes in a HQL call in order to match the result against a list of values.

The HQL as follows is what I thought would work using the || operator, but it does not as SQL Server does not recognise it.

select distinct p from POF p join p.authorisers a where (((p.countryCode || '\' || p.departmentCode) in ('GBR\31','GBR\61')) order by p.created asc

Hibernate version:
2.1.4
Mapping documents:
N/a
Code between sessionFactory.openSession() and session.close():
N/a
Full stack trace of any exception that occurs:
N/a
Name and version of the database you are using:
N/a
The generated SQL (show_sql=true):

select distinct top 10 pof0_.pof_id as pof_id, pof0_.created as created, pof0_.status as status, pof0_.reference as reference, pof0_.priority as priority, pof0_.external_id as external6_, pof0_.proxy as proxy, pof0_.proxy_full_name as proxy_fu8_, pof0_.proxy_email as proxy_em9_, pof0_.originator as originator, pof0_.originator_full_name as origina11_, pof0_.originator_email as origina12_, pof0_.department as department, pof0_.department_code as departm14_, pof0_.country_code as country15_, pof0_.authoriser as authoriser, pof0_.currency_code as currenc17_, pof0_.applies_to as applies_to, pof0_.net_cost as net_cost, pof0_.discount_cost as discoun20_, pof0_.carriage_cost as carriag21_, pof0_.tax_rate as tax_rate, pof0_.tax_cost as tax_cost, pof0_.gross_cost as gross_cost, pof0_.notes as notes, pof0_.supplier_id as supplie26_, pof0_.delivery_address_id as deliver27_ from dbPOF.dbo.tbl_POFs pof0_ inner join dbPOF.dbo.tbl_Authorisers authoriser1_ on pof0_.pof_id=authoriser1_.pof_id where ((((pof0_.country_code||'\'||pof0_.department_code)in('GBR\31' , 'GBR\61'))order by pof0_.created asc )

Debug level Hibernate log excerpt:
N/a

i managed to get this working using a + symbol instead. seems that the || is not interpreted to a + in the SQL Server dialect?

http://www.hibernate.org/89.html

|| should be parsed, strange

have you tried with named parameter + ||

i'll test it this this night

Try a current hibernate version first.