NHibernate COALESCE

Question

Hi!

Is it possible to translate this sql-statement to a CreateCriteria and second does NHibernate deals with ORDER BY COALESCE?

SELECT obs1.OBSLOPNR, obs1.LOKALLOPNR, obs2.OBSLOPNR, obs2.LOKALLOPNR
FROM
   (SELECT * FROM OBS WHERE OBS.LOKALLOPNR = 9) AS obs1 
FULL OUTER JOIN
   (SELECT * FROM OBS WHERE OBS.LOKALLOPNR = 8) AS obs2 
ON obs1.ARTLOPNR = obs2.ARTLOPNR
ORDER BY COALESCE(obs1.OBSLOPNR, obs2.OBSLOPNR)

Answer 1

I do not know about CreateCriteria, but Hibernate/HQL supports COALESCE, IFNULL, or ISNULL Depending on your underlying database only in the Where Clause.

You may be able to define a function in the dialect and it should also work in the select clause. I have not Tested This

See: http://www.hibernate.org/hib%5Fdocs/v3/reference/en/html/queryhql.html