OK. I have checked again the query and I cannot figure out, why
instead of the totals, I always get "NULL"
For example, if I use a "simple" CASE statement, the result is OK:
IList query = session.CreateQuery("select account.Number,
account.Description,
sum(case
when entry.IsDebit = 1 then case when 1=1 then 2 else 4 end else 3
end)
from LedgerEntry
entry
inner join
entry.AccountId account
inner join
account.AccountTypeId accountType
group by
account.Number,
account.Description
order by
account.Number")
.List();
If I use my original query, the third column is always generated with
"NULL".
I was wondering whether nested CASE statement are supported/working. I
also installed the v2.1.1, but the result did not change.
Any help is really appreciated.
Thanks.
Mike
On Nov 9, 4:43 pm, Oskar Berggren <[email protected]> wrote:
> Is case supported in HQL (for your version of NHibernate)?
>
> /Oskar
>
> 2009/11/9 mikeusdev <[email protected]>:
>
>
>
> > You are right, sorry.
>
> > ISession session = factory.OpenSession();
>
> > StringBuilder sb = new StringBuilder();
> > sb.Append("select account.Number, ");
> > sb.Append(" account.Description, ");
> > sb.Append(" sum(case ");
> > sb.Append(" when entry.IsDebit = 1 then ");
> > sb.Append(" case ");
> > sb.Append(" when entry.YearId < 6 and
> > accountType.ResetBalanceYearly = 1 then 0 ");
> > sb.Append(" when entry.YearId < 6 and
> > accountType.ResetBalanceYearly = 0 then entry.Amount ");
> > sb.Append(" when entry.YearId = 6 then
> > entry.Amount ");
> > sb.Append(" end ");
> > sb.Append(" else ");
> > sb.Append(" case ");
> > sb.Append(" when entry.YearId < 6 and
> > accountType.ResetBalanceYearly = 1 then 0 ");
> > sb.Append(" when entry.YearId < 6 and
> > accountType.ResetBalanceYearly = 0 then entry.Amount * -1 ");
> > sb.Append(" when entry.YearId = 6 then
> > entry.Amount * -1 ");
> > sb.Append(" end ");
> > sb.Append(" end) ");
> > sb.Append(" from LedgerEntry entry ");
> > sb.Append(" inner join entry.AccountId account ");
> > sb.Append(" inner join account.AccountTypeId accountType
> > ");
> > sb.Append(" order by account.Number ");
> > sb.Append(" group by account.Number, ");
> > sb.Append(" account.Description");
>
> > IList query = session.CreateQuery(sb.ToString())
> > .List();
>
> > On Nov 9, 4:06 pm, Oskar Berggren <[email protected]> wrote:
> >> Formatting that to actually be readable might be a good idea.
>
> >> /Oskar
>
> >> 2009/11/9 mikeusdev <[email protected]>:
>
> >> > Hi,
>
> >> > I am getting an error message when I try to execute the following
> >> > query:
>
> >> > IList query = session.CreateQuery(@"select account.Number,
> >> > account.Description, sum(case when (entry.IsDebit = 1m) then case when
> >> > (entry.YearId < 6 and accountType.ResetBalanceYearly = 1) then 0m when
> >> > (entry.YearId < 6 and accountType.ResetBalanceYearly = 0) then
> >> > entry.Amount when (entry.YearId = 6) then entry.Amount end else case
> >> > when (entry.YearId < 6 and accountType.ResetBalanceYearly = 1) then 0m
> >> > when (entry.YearId < 6 and accountType.ResetBalanceYearly = 0) then
> >> > entry.Amount * -1 when (entry.YearId = 6) then entry.Amount * -1 end
> >> > end) as entry.Amount from LedgerEntry entry inner join entry.AccountId
> >> > account inner join account.AccountTypeId accountType group by
> >> > account.Number, account.Description order by account.Number")
> >> > .List();
>
> >> > The error message is the following:
>
> >> > '(' expected after HQL function in SELECT [select account.Number,
> >> > account.Description, sum(case when (entry.IsDebit = 1m) then case when
> >> > (entry.YearId < 6 and accountType.ResetBalanceYearly = 1) then 0m when
> >> > (entry.YearId < 6 and accountType.ResetBalanceYearly = 0) then
> >> > entry.Amount when (entry.YearId = 6) then entry.Amount end else case
> >> > when (entry.YearId < 6 and accountType.ResetBalanceYearly = 1) then 0m
> >> > when (entry.YearId < 6 and accountType.ResetBalanceYearly = 0) then
> >> > entry.Amount * -1 when (entry.YearId = 6) then entry.Amount * -1 end
> >> > end) as entry.Amount from Oberon.Model.BusinessModel.LedgerEntry entry
> >> > inner join entry.AccountId account inner join account.AccountTypeId
> >> > accountType group by account.Number, account.Description order by
> >> > account.Number]
>
> >> > Does anyone know what the problem is? I tried various combinations
> >> > (column aliases, for example), but I cannot see where the problem
> >> > lies.
>
> >> > Thanks.
> >> > Mike
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
