Hello,

i am searching for a query like this:

select count(VoArbeitsfolgeAuftrag.*) from VoArbeitsfolgeAuftrag, Kunde
where ... and i_Afo_Datum>Date(now).submonth(VoKunde.faiMonate)

is that possible?

Hibernate version: 3.0.5

Mapping documents:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="de.test.pps.VoKunde" table="Kunde">
<meta attribute="class-description">
Repräsentiert einen Kunden in der test Datenbank
</meta>
<id name="voKundeKey" column="dbKeyKunde" type="integer"
length="4">
<generator class="native"></generator>
</id>

<natural-id>
<property name="mandant" type="int" column="mandant">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="kundenNummer" type="int"
column="kundennummer">
<meta attribute="use-in-tostring">true</meta>
</property>
</natural-id>
<version name="version" column="version"></version>
<property name="anrede" type="string" not-null="true"
length="10">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="unsereLieferantenNummer" type="string"
not-null="true" length="20">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="kundenName" type="string" not-null="true"
length="30">
<meta attribute="use-in-tostring">true</meta>
</property>

<property name="kundenVorName" type="string" length="30">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="strasse" type="string" length="30">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="land" type="string" length="30">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="postleitzahl" type="integer" length="6">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="ort" type="string" length="30">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="telefon1" type="string" length="30">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="telefax1" type="string" length="30">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="telefon2" type="string" length="30">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="telefax2" type="string" length="30">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="ansprechpartner" type="string" length="30">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="telefon3" type="string" length="30">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="eMail" type="string" length="60">
</property>
<property name="bemerkungen" type="string">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="standardFormularQSFreigabe" type="string"
length="30">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="fai" type="boolean">
<meta attribute="use-in-tostring">true</meta>
</property>
<property name="faiMonate" type="integer" length="2">
<meta attribute="use-in-tostring">true</meta>
</property>
</class>

</hibernate-mapping>


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="de.leuka.pps.VoArbeitsfolgeAuftrag" table="Afo_Auftrag">
<meta attribute="class-description">
Repräsentiert eine Arbeitsfolgeerweiterungen eines Auftrages in der test Datenbank
</meta>
<id name="voArbeitsFolgeAuftragKey" column="dbKeyAfoAuftrag" type="integer" length="10" access="property">
<generator class="native"></generator>
</id>

<natural-id>
<property name="mandant" type="integer" column="mandant">
<meta attribute="use-in-tostring">true</meta>
</property>
<many-to-one name="auftrag" class="de.test.pps.VoAuftrag" column="Auftrag" lazy="false"></many-to-one>
<many-to-one name="afo" class="de.test.pps.VoArbeitsfolge" column="Arbeitsfolge" lazy="false"></many-to-one>

</natural-id>
<version name="version" column="version"></version>

<many-to-one name="i_Afo_Pruefer" class="de.test.pps.VoPersonal2" column="Pruefer" lazy="false"></many-to-one>
<property name="i_Afo_erledigt" type="boolean" column="erledigt"></property>
<property name="i_Afo_Datum" type="date"></property>
<property name="i_Afo_WeiterleitungsMengeGesamt" type="integer" column="Weiter_Menge"></property>
<property name="i_Afo_Gut_Menge" type="integer" column="Gut_Menge"></property>
<property name="i_Afo_Beanstandete_Menge" type="integer" column="Beanstandet_Mg"></property>
<property name="i_Afo_Ausschuss_Menge" type="integer" column="Ausschuss_Mg"></property>
<property name="fai" type="boolean" column="fai"></property>
</class>

</hibernate-mapping>



Name and version of the database you are using:

Mysql 4.1.14

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

sure, just take a look at the reference-docu, chapter 15.5 and 15.8.,
there you'll find the aggregate functions and expressions




...

Thanks for your reply!

I know the docu, but there is nothing to calculate with dates, especially subtracting months in HQL.

Please forgive me if i have posted this post it in the wrong place.

I have a problem with sum function (data calculation). My lucky guess is that the column in the db is type "Short" while the sum returns greater than Short. Is this correct? Do i have to change the columns to "Long" to get back a big number? My DB is Oracle9i, jdk 1.4, Hibernate 3.1

I am using the following code that works:



BUT Works 5 out of 6.

The last loop the following exception is thrown:

Please redirect me if the answer exists already. I tried to search but i didn't manage to find a solution.


The exception is:
DEBUG: could not execute query using iterate
org.hibernate.exception.GenericJDBCException: could not execute query using iterate
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:91)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:79)

Hello again,

This was indeed the problem.

I increased the size of the column to NUMBER(10) (reversed engineered classes e.t.c) and the query is running properly.

Please tell me if you have more information on this issue, because i would like to have a more in-depth knowledge of the certain issue.