Filtering a projection like "HAVING" in SQL

[GHAZALI_Abdesselam]

I am looking for a way to filter projection results in the following code:

  result := Manager.Find<TCptMvt>
              .Where(Linq.IsNotNull('APURID'))
              .CreateAlias('ApurID', 'aa')
              .CreateAlias('CptID', 'cc')
              .Select(Tprojections.ProjectionList
                .Add(Linq['cc.CPTREF'].As_('CPTREF'))
                .Add(Linq['aa.LET'].As_('LET'))
                .Add(Linq['aa.ApurID'].As_('APURID'))
                .Add(Tprojections.Sql<currency>('SUM((1-2*{CR})*{MONTANT})').As_('TOTAL'))
                .Add(Linq['cc.CPTREF'].Group).Add(Linq['aa.LET'].Group).Add(Linq['aa.ApurID'].Group)
              ).Where(TLinq.Neq(TProjections.Sql<currency>('{TOTAL}'), 0))
              .AddOrder(TOrder.Asc('cc.CPTREF'))
              .ListValues;

In this case, I would like to retrieve only rows where Total ('SUM((1-2*{CR})*{MONTANT})') is not equal to 0
Does anyone with more talent have an idea? Thanks

[wlandgraf (Wagner Landgraf)]

You can try

.Where(((Linq.Literal(1) - Linq.Literal(2) * Linq['CR']) * Linq['MONTANT']).Sum <> 0)

or

.Where(Linq.Neq(
   TProjections.Sum(
     TProjections.Sql<Currency>('((1-2*{CR})*{MONTANT})'))
  , 0))

The Sum being explicit outside the SQL expression will make Aurelius put the condition expression in HAVING clause automatically.

[GHAZALI_Abdesselam]

The first form with Literal raises ENonPublicType expcetion "Type ':2' s not declared in the inteface section of the unit":

result := Manager.Find<TCptMvt>
          .Where(Linq.IsNotNull('APURID'))
          .CreateAlias('ApurID', 'aa')
          .CreateAlias('CptID', 'cc')
          .Select(Tprojections.ProjectionList
            .Add(Linq['cc.CPTREF'].As_('CPTREF'))
            .Add(Linq['aa.LET'].As_('LET'))
            .Add(Linq['aa.ApurID'].As_('APURID'))
            .Add(Tprojections.Sql<currency>('SUM((1-2*{CR})*{MONTANT})').As_('MONTANT'))
            .Add(Linq['cc.CPTID'].As_('CPTID'))
            .Add(Linq['cc.CPTREF'].Group).Add(Linq['aa.LET'].Group)
            .Add(Linq['aa.ApurID'].Group).Add(Linq['cc.CPTID'].Group)
          )
           .Where(((Linq.Literal(1) - Linq.Literal(2) * Linq['CR']) * Linq['MONTANT']).Sum <> 0)
          .AddOrder(TOrder.Asc('cc.CPTREF'))
          .ListValues;

The second form do not filter zeroed values :

result := Manager.Find<TCptMvt>
          .Where(Linq.IsNotNull('APURID'))
          .CreateAlias('ApurID', 'aa')
          .CreateAlias('CptID', 'cc')
          .Select(Tprojections.ProjectionList
            .Add(Linq['cc.CPTREF'].As_('CPTREF'))
            .Add(Linq['aa.LET'].As_('LET'))
            .Add(Linq['aa.ApurID'].As_('APURID'))
            .Add(Tprojections.Sql<currency>('SUM((1-2*{CR})*{MONTANT})').As_('MONTANT'))
            .Add(Linq['cc.CPTID'].As_('CPTID'))
            .Add(Linq['cc.CPTREF'].Group).Add(Linq['aa.LET'].Group)
            .Add(Linq['aa.ApurID'].Group).Add(Linq['cc.CPTID'].Group)
          )
          .Where(TLinq.Neq(TProjections.Sql<currency>('((1-2*{CR})*{MONTANT})'), 0))
          .AddOrder(TOrder.Asc('cc.CPTREF'))
          .ListValues;

[GHAZALI_Abdesselam]

Hi,

I apologize, I made a mistake in the transcription for the second syntax, wich works. For the first one, we have to specify explicit type with {integer}:

.Where(((Linq.Literal<integer>(1) - Linq.Literal<integer>(2) * TLinq['CR']) * TLinq['MONTANT']).Sum <> Linq.Literal<integer>(0))

Thank you