Hello,

I am using hibernate 2.1.3, in jboss, with Informix. I have created a formula field to substract to date columns to find the number of days. This is how the propery is declared in the hbm:

<property name="delDaysInYard" update="false" insert="false" type="java.lang.Long" formula="replace(substr(start-end, -12, 3),' ','0')"/>

When the property is in the select column there is no problem, but when the property is in the order by clause I get a syntax error.

When Hibernate creates the SQL it places the formula into the SQL twice in the select clause and in the order by clause. If hibernate would use the alias it created for the property in the select clause, it would not only be more efficient the query would actually work.

Is there some attribute to make this work?

This sounds like a bug, but I'm not sure if its Hibernates fault. The database should be able to deal with both, but that might be database dependent. Please open a JIRA issue.

I attempted to put a bug into the JIRA, bug it was closed immediately with the comment:
"You don''t have permission to work on this issue."

So, hopefully, you can enter the following bug for me:

I have two date columns in my table, and I have created a third computed column to get difference of the two. The third column is defined in the hbm like this:

<property name="delDays" update="false" insert="false" type="java.lang.String" formula="(start - end)"/>

When the property is in the select column there is no problem, but when the property is in the order by clause I get a syntax error.

This is the SQL that hibernate produces:

select delivery0_.del_delivery_num as x0_0_, (delivery0_.end - delivery0_.start) as x1_0_ from delivery delivery0_ where (delivery0_.del_del_stat_code<>'CAN') order by (delivery0_.end - delivery0_.start);

Hibernate should recognize that some databases require that the field in the order by clause must be in the select clause. Additionally, hibernate should use the alias it created for the property in the select clause, it would not only be more efficient the query would actually work!

If hibernate produced the following instead, the query would work:
select delivery0_.del_delivery_num as x0_0_, (delivery0_.end - delivery0_.start) as x1_0_ from delivery delivery0_ where (delivery0_.del_del_stat_code<>'CAN') order by x1_0_;