0

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)
bdukes
  • 152,002
  • 23
  • 148
  • 175
Mats
  • 59
  • 1
  • 5
  • the situation like this we will always make it a store procedure, and then use session.getNameQuery() to call that procedure. Why don't we make things easy? – Xiaolong Liu Aug 21 '17 at 18:26

2 Answers2

0

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_docs/v3/reference/en/html/queryhql.html

Jason Von Ruden
  • 184
  • 1
  • 5
  • 10
0

There is no inbuilt support. You can however create your own Order class. Here is a good example.

You could either create a class that accepts any string of sql, or you could try and create a CoalesceOrder class.

Community
  • 1
  • 1
cbp
  • 25,252
  • 29
  • 125
  • 205