Problem with property & formula

oren · **Joined:** Wed Mar 03, 2004 6:38 am **Posts:** 26

Hi,

I have this class

Code:

<class name="OrderHeader" table="REORDH">
...
<property name="statusDescription" formula="(select CLTEXT from RESCOL where CLLIST = 1005 AND CLVALN = 10)" />
</class>

i want to get the value of statusDescription from different table.

This is the sql hibernate is generating:

Code:

select 
   orderheade0_.RHCUST= as RHCUST,
   orderheade0_.RHORNO as RHORNO, 
   orderheade0_.RHCNSL as RHCNSL, 
   orderheade0_.RHOSTS as RHOSTS, 
   (select orderheade0_.CLTEXT from RESCOL where orderheade0_.CLLIST = 1005 AND orderheade0_.CLVALN = 10) as f0_ 
from REORDH orderheade0_ 
where (orderheade0_.RHCUST=? )

As you can see, the inner select is not what i expected.

I tried puuting alias but no change.

I tried to use other maped objects in the inner select but it didn't help as well.

what am i doing wrong?

david · **Posted:** Thu Apr 08, 2004 9:01 am

Formular is designed to provide a non-persistent property based on (usually only) existing fields. Here is a simple example:

<property name="sumval" formula="firstField + secondField" />

The formula is added to the select clause of the SQL to obtain the result. It is limited to what is valid as a expression within the select clause for the target database.

anthony · **Posted:** Thu Apr 08, 2004 9:07 am

excuse me but i think this is possible since i doing things like this, just add an alias, it shoud work

Code:

<class name="OrderHeader" table="REORDH"> 
... 
<property name="statusDescription" formula="(select a.CLTEXT from RESCOL a where a.CLLIST = 1005 AND a.CLVALN = 10)" /> 
</class> 

david · **Posted:** Thu Apr 08, 2004 9:19 am

I did say, if its valid in the select clause then its valid as the formula. Hibernate just passes it through to the database as straight SQL. I did not check the query closely - should have picked up the alias reference but I was not incorrect.

anthony · **Posted:** Thu Apr 08, 2004 9:22 am

i did not say you were incorrect :))

david · **Posted:** Thu Apr 08, 2004 9:24 am

oren · **Joined:** Wed Mar 03, 2004 6:38 am **Posts:** 26

delpouve wrote:

excuse me but i think this is possible since i doing things like this, just add an alias, it shoud work

Code:

<class name="OrderHeader" table="REORDH"> 
... 
<property name="statusDescription" formula="(select a.CLTEXT from RESCOL a where a.CLLIST = 1005 AND a.CLVALN = 10)" /> 
</class> 

Yes it is working. Thank you so you are magician :-)

anthony · **Posted:** Thu Apr 08, 2004 9:31 am

no no, Hibernate is Magic not me

oren · **Joined:** Wed Mar 03, 2004 6:38 am **Posts:** 26

delpouve wrote:

no no, Hibernate is Magic not me

Ok :-)