i am new to NHibernate and Hibernate Query Language, i have the following two tables:
Code:
table Categories(
Category_Id int primary key,
Cat_Type_Id int foreign key references Category_Types(Cat_Type_Id),
Category_Name varchar(20) not null,
Mode int not null /* 1:monthly 0:daily 2:multi months */
)
table Budget (
Budget_Id int primary key,
Category_Id int foreign key references Categories(Category_Id),
Budget_Money money not null,
Date_From date not null,
Date_To date not null
)
mapped to the following two classes:
Code:
public class Budget
{
int _Budget_Id;
public virtual int Budget_Id
{
get
{
return _Budget_Id;
}
set
{
_Budget_Id = value;
}
}
Categories _Category;
public virtual Categories Category
{
get
{
return _Category;
}
set
{
_Category = value;
}
}
decimal _Budget_Money;
public virtual decimal Budget_Money
{
get
{
return _Budget_Money;
}
set
{
_Budget_Money = value;
}
}
DateTime _Date_From;
public virtual DateTime Date_From
{
get
{
return _Date_From;
}
set
{
_Date_From = value;
}
}
DateTime _Date_To;
public virtual DateTime Date_To
{
get
{
return _Date_To;
}
set
{
_Date_To = value;
}
}
}
public class Categories
{
int _Category_Id;
public virtual int Category_Id
{
get
{
return _Category_Id;
}
set
{
_Category_Id = value;
}
}
string _Category_Name;
public virtual string Category_Name
{
get
{
return _Category_Name;
}
set
{
_Category_Name = value;
}
}
int _Mode;
public virtual int Mode
{
get
{
return _Mode;
}
set
{
_Mode = value;
}
}
Iesi.Collections.Generic.ISet<Budget> _Budgets;
public virtual Iesi.Collections.Generic.ISet<Budget> Budgets
{
get
{
return _Budgets;
}
set
{
_Budgets = value;
}
}
}
how can i write this following sql query using hql :
Code:
select @Total_Budget = sum(Budget.Budget_Money * case when Categories.Mode = 0 then DATEDIFF(D,@From,@To) Else 1 end ) from Budget right join Categories on Categories.Category_Id = Budget.Category_Id and Budget.Date_From = @From and Budget.Date_To = @To
i tried to write something like :
Code:
return (decimal)Session.CreateQuery("select sum(b.Budget_Money * case when c.Mode = 0 then (b.Date_From - b.Date_To) else 1 end ) from Budget b join b.Category c where b.Date_From = :datefrom and b.Date_To = :dateto")
.SetParameter("datefrom", datefrom)
.SetParameter("dateto", dateto).UniqueResult();
when testing the previous code i got the following Exception
Code:
could not execute query [ select sum(budget0_.Budget_Money*case when categories1_.Mode=0 then budget0_.Date_From-budget0_.Date_To else 1 end) as col_0_0_ from Budget budget0_ inner join Categories categories1_ on budget0_.Category_Id=categories1_.Category_Id where budget0_.Date_From=@p0 and budget0_.Date_To=@p1 ]
so is that becouse of the case statement or what???
Thanks;