Property Formula bug?

robcar · **Joined:** Tue Feb 24, 2009 4:44 am **Posts:** 4

Hello
I'm quite a beginner with NHibernate but I have run into a problem I
can't seem to come any closer to fix. Is this a bug?

I'm trying to make my self a class that looks like the following:

Code:

    public class Price 
    { 
        public virtual int PriceId { get; set; } 
        public virtual int PerQuantityMultiplier { get; set; } 
        public virtual int Generation { get; set; } 
        public virtual DateTime EffectiveDate { get; set; } 
    } 

With the following mapping file:

Code:

<?xml version="1.0" encoding="utf-8" ?> 
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" > 
  <class name="Volvo.Purchasing.Pricing.DomainModel.Entities.Price, 
Entities" > 
    <id name="PriceId" column="PriceId" type="System.Int32" unsaved- 
value="null"> 
      <generator class="native"/> 
    </id> 
    <property name="PerQuantityMultiplier" column="Multiplier" not- 
null="false" type="System.Int32"  insert="true" update="true"/> 
    <property name="EffectiveDate" column="EffectiveDate" not- 
null="true" type="System.DateTime"  insert="true" update="true"/> 
    <property name="Generation" column="Generation" not-null="true" 
type="System.Int32" formula="ROW_NUMBER() OVER (PARTITION BY 
AgreementId ORDER BY EffectiveDate DESC) "  /> 
  </class> 
</hibernate-mapping> 

When I fetch a list of this class I get an "Can''t execute query
exception". This is because NHibernate prefixes my Formula query and
doesn't seem to recognize the reserved words PARTITION and OVER.

The SQL NHibernate tries to execute as a result of the configuration
(with the formula) is this:

Code:

SELECT this_.PriceId as PriceId0_0_, 
        this_.Multiplier as Multiplier0_0_, 
        this_.EffectiveDate as Effectiv3_0_0_, 
        (ROW_NUMBER() [b]this_.[/b]OVER ([b]this_.[/b]PARTITION BY this_.AgreementId ORDER 
BY this_.EffectiveDate DESC)) as formula0_0_ 
FROM Price this_ 

I just need to change this to:

Code:

SELECT this_.PriceId as PriceId0_0_, 
        this_.Multiplier as Multiplier0_0_, 
        this_.EffectiveDate as Effectiv3_0_0_, 
        (ROW_NUMBER() OVER (PARTITION BY this_.AgreementId ORDER BY 
this_.EffectiveDate DESC)) as formula0_0_ 
FROM Price this_

Is there anyway to stop NHibernate from being "smart", to tell NHibernate not to do this replacment of my custom SQL query? Or to do some escape sequence for the reserved words?

Or to hook in to the executing of the SQL Query and just do a string replace of what is wrong?

Very, very greatful for any help in this matter.

KR
Robin

wolli · **Posted:** Fri Mar 06, 2009 12:43 pm

Try

Code:

formula="(ROW_NUMBER() OVER (PARTITION BY 
{alias}.AgreementId ORDER BY {alias}.EffectiveDate DESC))"

robcar · **Joined:** Tue Feb 24, 2009 4:44 am **Posts:** 4

Thanks for the answer wolli.

Unfortunately it didn't work, NHibernate still puts in Alias before OVER and PARTION BY like below.

(ROW_NUMBER() this_.OVER (this_.PARTITION BY {this_}.AgreementId ORDER BY {this_}.EffectiveDate DESC)) as formula1_0_

(I also tried as you suggested with {alias} but with the same result)

robcar · **Joined:** Tue Feb 24, 2009 4:44 am **Posts:** 4

It seems that this is a confirmed bug and should from what I can tell be fixed in the trunk version.
http://jira.nhibernate.org/browse/NH-1617

To show myself as the complete newbie I am. Can anyone point me to some instructions on how to download and compiler this version?

KR
Robin

mihaic79 · **Joined:** Thu Apr 29, 2010 5:59 am **Posts:** 1

I had the same problem with 'this_.' being added in front of 'OVER' keyword.

My formula was simpler: 'COUNT(*) OVER ( )'

I got around this by writing the formula without spaces 'COUNT(*)OVER()' apparently ORACLE can parse it correctly and hibernate is confused enough not to add 'this_.' prefix.

Regards,
Mihai.

MateusBahis · **Joined:** Tue May 11, 2010 11:12 am **Posts:** 1

extend the class dialect of the db and add RegisterKeyword ("over").
use lowercase in sql.

<property name="dialect">
new dialect extended
</property>