I have got a problem to create a select with a formated DateTime.

Trial 1:
dim sql as string = "SELECT AVG(x.Short) , AVG(x.Month) , Date_Format(x.DateTime, '%H') as Time from History x GROUP BY Time "
Dim list As IList = Session.CreateQuery(sql).List

Trial 2:
dim sql as string = "SELECT AVG(x.Short) , AVG(x.Month) , Date_Format(x.DateTime, '%H') from History x GROUP BY Date_Format(x.DateTime, '%H') "
Dim list As IList = Session.CreateQuery(sql).List


Error:
Both times I get the same error.
undefined alias or unknown mapping: Date_Format [SELECT AVG(x.Short) , AVG(x.Month) , Date_Format(x.DateTime, '%H') as Time from History x GROUP BY Time ]


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="History, History" table="history" >
<id name="ID" column="ID" type="Int32" unsaved-value="0">
<generator class="identity" />
</id>
<many-to-one name="History" column="ID_RefHistory " class="History, History" fetch="join"/>
<property name="DateTime" column="DateTime" type="DateTime" />
<property name="Short" column="Short" type="Int32" length="4" />
<property name="Month" column="Month" type="Int32" length="4" />
</class>
</hibernate-mapping>

Does anybody can tell me what I have to do, if I want to group all values to get a hourly result?

[/code]

I don't think that this can be done with HQL. You need to use a ISQLQuery instead:



where the scalar names are the alias names of the columns.

There is no method with only one parameter for CreateSQLQuery. I have to use:
CreateSQLQuery(sql as string, returnAliases() as string, returnClasses as System.Type)

Trial:
Dim columnarray(2) As String
Dim classes(2) As System.Type
columnarray(0) = "Short"
columnarray(1) = "Month"
columnarray(2) = "Time"
classes(0) =GetType(Double)
classes(1) =GetType(Double)
classes(2) =GetType(Double)

Dim list As IList = Session.CreateSQLQuery(sql, columnarray, classes).List

Error:

Unknown entity class: System.Double

What's wrong with System.Double?

Looks like you have an older version of hibernate. I've no clue, sorry. The code looks good to me.

I'm using NHibernate Version 1.1.

Can I update the Version without any changes of my programs?

No, I'm afraid there are some breaking changes. I can't remember all, but here are some:

- xmlns="urn:nhibernate-mapping-2.2" in the mapping definition has changed from 2.0 to 2.2
- collections are lazy loaded by default
- maybe some more ...

But if you're project isn't already to far evolved it might be worth changing. Think about Nhibernate 2.0 the, which should be GA soon.

Ok, I changed now the NHibernate version to the newest one, but I still have a problem.

It is possible for me now to execute the following:

sess.CreateSQLQuery("your sql")
.AddScalar("Short", NHibernateUtil.Int32)
.AddScalar("Month", NHibernateUtil.Int32)
.AddScalar("Time", NHibernateUtil.String);

But I'm not familiar with the result. I want to add the result to a chart and therefore I need objects with properties, to add a property as a serie to the chart. I tried to use .AddEntity, but I think that is not what I need. I also got an error with .AddEntity.

Any suggestions what I can do, if I don't want the properties listed by indices?

Now I got something like this:
obj(0) => (0) = 3 //Would be "Short"
obj(0) => (1) = 5 //Would be "Month"
obj(0) => (2) = xx.xx.xxxx //Would be "Time"

But I need something like this:
obj(0) => Short = 3 //Would be "Short"
obj(0) => Month = 5 //Would be "Month"
obj(0) => Time = xx.xx.xxxx //Would be "Time"

Sounds like it's exactly what you need. Is it possible that you used an unmapped entity ? If you have an unmapped entity you have to do something like this:

Yes, you are right. I used unmapped entities. But I have to use unmapped entities. I want to create a chart and I create queries where I get "month"-values and "short"-values for each series of the chart (ShortSeries1, MonthSeries1, ShortSeries2, MonthSeries2, ShortSeries3, MonthSeries3.....ShortSeriesX, MonthSeriesX). I don't want to define a property for each possible resultvalue. Isn't there another possiblity?

Hmm ... you can try and write your own result transformer (IResultTransfomer).