coalesce and oracle8
Description
Activity
GavinGMay 31, 2005 at 7:21 PM
Actually the problem here is that coalesce() is indeed a portable HQL function, as of 3.0.4. But the user has not bothered to test on 3.0.5, where coalesce() does indeed translate to nvl() on Oracle.
Maciej LopacinskiMay 31, 2005 at 12:42 PM
I did not want to offend you guys, really I think you do lot of good work and lot of people use Hibernate in many projects (me too). Thanks again :]
EOT.
Christian BauerMay 31, 2005 at 12:36 PM
No, we are actually very clueless guys and just fake it...
Maciej LopacinskiMay 31, 2005 at 12:32 PM
Okay, thank you for your answers.
IMHO I think that if you write in documentation that this dialect can be used with any oracle version, and in other place (http://tinyurl.com/bko7q) you write that I can use coalesce function then I expect that it will work without creating new dialect, and it will work with every supported database.
In Hibernate in Action one may find chapter which points that hibernate developers knows lots about database, performance and differences between RDMSes and I think thath nvl semantic is the same as coalesce so it should be registered in OracleDialect provided by you.
Thanks for Hibernate, it is really good product.
ps. Your answers are comming pretty fast.
Christian BauerMay 31, 2005 at 12:21 PM
Thats why its an extension interface and behold, it works as expected if you try to extend it for your case 
Details
Assignee
UnassignedUnassignedReporter
Maciej LopacinskiMaciej LopacinskiComponents
Priority
Major
Details
Details
Assignee
Reporter
Components
Priority
Major
Oracle8 do not have coalesce built-in function, instead one must use nvl function.
My query was:
from EventControl c where c.eventCollection = :collection and (
(c.event.startDate < :beginTime and coalesce(c.event.endDate, c.event.startDate) > :beginTime) or (c.event.startDate between :beginTime and :endTime)) order by c.event.startDate
Which was translated (with org.hibernate.dialect.OracleDialect) to:
Hibernate: select eventcontr0_.event_control_id as event1_, eventcontr0_.collection_id as collection2_3_, eventcontr0_.event_id as event3_3_, eventcontr0_.visible as visible3_, eventcontr0_.editable as editable3_ from event_control eventcontr0_, event event1_ where eventcontr0_.event_id=event1_.event_id and eventcontr0_.collection_id=? and (event1_.start_date<? and coalesce(event1_.end_date, event1_.start_date)>? or event1_.start_date between ? and ?) order by event1_.start_date
In documentation one may found:
http://www.hibernate.org/hib_docs/v3/reference/en/html/session-configuration.html#configuration-optional-dialects
Oracle (any version) org.hibernate.dialect.OracleDialect
Update documentation or add default query substitution coalesce -> nvl.