Having com dois predicados Sum() == 0 or Sum() is null - queryover

[glautter neves]

Olá pessoal,

Alguém tem alguma dica para montar um queryover com a cláusula Having, mas com duas condições?

ex.:

group by ...

Having (sum(valor) == 0) OR (sum(valor) IS NULL);

Já fiz assim:

.Where(Restrictions.Eq(Projections.Sum<MinhaClasse>((x) => x.Valor), 0)))

SQL gerado: 

group by ...

Having (sum(valor) == 0)

Já fiz assim:

.Where(Restrictions.Or(Restrictions.Eq(Projections.Sum<MinhaClasse>((x) => x.Valor), 0),

                                      Restrictions.Eq(Projections.Sum<MinhaClasse>((x) => x.Valor), null));

SQL gerado:

Erro: Referência de objeto não definida para uma instância de um objeto. Porque o valor que estou comparando está nulo.

Mesmo assim o valor "Restrictions.Or" irá me retornar fora do "Having". Será um "And".

O que eu preciso é que a segunda opção (Restrictions.Eq(Projections.Sum<MinhaClasse>((x) => x.Valor), null)) me traga sum(valor) is null;

Alguém tem alguma dica?

desde já agradeço

[Michael Powell]

English please. Thank you.

[Sruti S]

Hi:

OR (sum(valor) IS NULL

Why are you testing the result of an aggregate function, especially SUM for NULL? In SQL, aggregate functions like sum() normally ignore NULLs.  sum(x) should normally never return NULL. If you do get NULLs at the C# level, there are several ways to handle that.

HTH.

On Thu, May 7, 2015 at 6:29 AM, Michael Powell <mwpow...@gmail.com> wrote:

English please. Thank you.

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+u...@googlegroups.com.
To post to this group, send email to nhu...@googlegroups.com.
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.

[glautter neves]

Sorry, confused with another group.

I have solved this case in bold, but is ignoring the "Having" and generating "And"

Having (sum (value) == 0) OR (sum (value) IS NULL);

using:

.Where(Restrictions.Eq(Projections.Sum<MinhaClasse>((x) => x.Valor), 0) || Restrictions.IsNull(Projections.Sum<MinhaClasse>((x) => x.Receita)))

maybe use a coalesce

translated text below:

Hello,

Does anyone have any tips to put together a queryover with the Having clause, but with two conditions?

ex .:

group by ...

Having (sum (value) == 0) OR (sum (value) IS NULL);

I've done like this:

.Where (Restrictions.Eq (Projections.Sum <MyClass> ((x) => x.Valor), 0)))

SQL generated:

group by ...

Having (sum (value) == 0)

I've done like this:

.Where (Restrictions.Or (Restrictions.Eq (Projections.Sum <MyClass> ((x) => x.Valor), 0)

                                       Restrictions.Eq (Projections.Sum <MyClass> ((x) => x.Valor), null));

SQL generated:

Error: Object reference not set to an instance of an object. Because the value that I'm comparing is null.

Yet the value "Restrictions.Or" will return me out of the "Having". It will be an "And".

What I need is that the second option (Restrictions.Eq (Projections.Sum <MyClass> ((x) => x.Valor), null)) bring me sum (amount) is null;

Does anyone have any tips?

I thank you

Em quinta-feira, 7 de maio de 2015 08:11:26 UTC-3, Michael Powell escreveu:

English please. Thank you.

[glautter neves]

Hi, 

I have made the sql out and is working, but I need to generate the queryover.

There are several relationships. This return is for a subquery and the sum is for a list that has not always record but may also exist records with zero values.

Thank you,

I have solved this case in bold, but is ignoring the "Having" and generating "And"

Having (sum (value) == 0) OR (sum (value) IS NULL);

using:

.Where(Restrictions.Eq(Projections.Sum<MinhaClasse>((x) => x.Valor), 0) || Restrictions.IsNull(Projections.Sum<MinhaClasse>((x) => x.Receita)))

maybe use a coalesce

[Gunnar Liljas]

Sum returns null if there is nothing to aggregate.

Maybe this will work:

.Where(Restrictions.Eq(Projections.SqlFunction("coalesce", NHibernateUtil.Object,Projections.Sum<MinhaClasse>(x => x.Valor), Projections.Constant(0)), 0));

In other words

having coalesce(sum(value),0)=0

[glautter neves]

Gunnar, very good,

but also does not generate "HAVING". Generated "And".

AND nvl(sum(classAlias.value), 0) = 0 

If I put just the condition of zero, it generates "HAVING".

Where(Restrictions.Eq(Projections.Sum<MyClass>((x) => x.Valor), 0))

thank you very much

[Gunnar Liljas]

OK, I never use QueryOver, so there may be issues (report to JIRA if you feel it's a bug). 

In Linq I would do something like

.Sum(x=>(decimal?)x.Valor).GetValueOrDefault(0)==0

[glautter neves]

It's right.

It can actually be a problem.

Thank you

[Alexander Zaytsev]

Can you please provide full queryover you are trying to build?

Best Regards,

Alexander

[glautter neves]

Hi,

I am sending a model simplified.

See if you can help.

public IEnumerable<DTOCompanyDataWithSumValue> GetCompanyFinancesWithoutValues(DTOParameter parameter)

{

  return Session.QueryOver<Company>(() => companyAlias)

                .Where(x => x.Month == parameter.Month)

                .And(x => x.Year == parameter.Year)

                .WithSubquery.WhereExists(QueryOver.Of<Company>(() => companyAlias2)

                        .Where(e => e.KeyCompany == companyAlias.KeyCompany)

                        .And(x => x.Month == parameter.Month)

.And(x => x.Year == parameter.Year)

                        .JoinQueryOver(x => x.Financial, JoinType.LeftOuterJoin)

                        .JoinQueryOver(x => x.FinanceRegions, () => financesRegionsAlias, JoinType.LeftOuterJoin) //List -- This may contain records with zero or may not contain records

                        .Where(Restrictions.IsNull(Projections.Sum<FinanceRegion>(x => x.Value)) || Restrictions.Eq(Projections.Sum<FinanceRegion>(x => x.Value), 0))

                        .Select(Projections.Sum<FinanceRegion>(x => financeRegionsAlias.Value).WithAlias(() => dtoCompany.SumValue),

                                Projections.Group(() => companyAlias2.KeyCompany).WithAlias(() => dtoCompany.KeyCompany),

                                Projections.Group(() => companyAlias2.CompanyName).WithAlias(() => dtoCompany.CompanyName))

                                )

                        .TransformUsing(Transformers.AliasToBean<DTOCompanyDataWithSumValue>())

                .List<DTOCompanyDataWithSumValue>().Distinct().ToList();

OR

  return Session.QueryOver<Company>(() => companyAlias)

                .Where(x => x.Month == parameter.Month)

                .And(x => x.Year == parameter.Year)

                .WithSubquery.WhereExists(QueryOver.Of<Company>(() => companyAlias2)

                        .Where(e => e.KeyCompany == companyAlias.KeyCompany)

                        .And(x => x.Month == parameter.Month)

.And(x => x.Year == parameter.Year)

                        .JoinQueryOver(x => x.Financial, JoinType.LeftOuterJoin)

                        .JoinQueryOver(x => x.FinanceRegions, () => financesRegionsAlias, JoinType.LeftOuterJoin) //List -- This may contain records with zero or may not contain records

                        .Where(Restrictions.Eq(Projections.Sum<FinanceRegion>(x => x.Value), 0)) || Restrictions.IsNull(Projections.Sum<FinanceRegion>((x) => x.Value)))

                        .Select(Projections.Sum<FinanceRegion>(x => financeRegionsAlias.Value).WithAlias(() => dtoCompany.SumValue),

                                Projections.Group(() => companyAlias2.KeyCompany).WithAlias(() => dtoCompany.KeyCompany),

                                Projections.Group(() => companyAlias2.CompanyName).WithAlias(() => dtoCompany.CompanyName))

                                )

                        .TransformUsing(Transformers.AliasToBean<DTOCompanyDataWithSumValue>())

                .List<DTOCompanyDataWithSumValue>().Distinct().ToList();

thanks