SQL HAVING Clause

Hi,
  How to implemente "Having" clause in Aurelius?
  Thanks

You don't need to. Just build the query naturally using projections for aggregated fields as usual and it will manage to put the correct expressions in either WHERE or HAVING clause.

Hi
  maybe i can turn around the code, but here is SQL:

  SELECT a.ClassXId, c.Description ,count(a.ID) as "Classes Count"
  FROM DaiCla a
  LEFT JOIN ClassX c on c.id=a.ClassXId
  WHERE a.ClassStart between '01.06.2017, 00:00:00.739' and '01.06.2017, 23:00:00.739'
    AND a.id in ( select b.DaiClaId  //HOW TO DO IN AURELIUS?
                  from DaiClaP b
                  where a.id=b.DaiClaId and b.ValidTicket=1
                  group by b.DaiClaID
                  having count(b.DaiClaID)>0 //HOW TO DO IN AURELIUS?
                )
  GROUP BY a.ClassXID, c.Description
  ORDER BY c.Description

  Aurelius:

  Result := TCriteria.Create(TDaiCla, Manager);

  Result.CreateAlias(Dic.DaiCla.ClassXId.AssociationName, 'AliasClassX')
        .Select(TProjections.ProjectionList
               .Add(TProjections.Group('AliasClassX.'+Dic.ClassX.Id.PropName))
               .Add(TProjections.Group('AliasClassX.'+Dic.ClassX.Description.PropName))
               .Add(TProjections.Count(Dic.DaiCla.id.PropName).As_('CountId'))
               )
        .Where(TLinq.GreaterOrEqual(Dic.DaiCla.ClassStart.PropName, StartOfTheDay(aStartDate)) and
               TLinq.LessOrEqual   (Dic.DaiCla.ClassStart.PropName, EndOfTheDay(aEndDate)))
        .OrderBy('AliasClassX.'+Dic.ClassX.Description.PropName);

  DaiCla and DaiClaP are a marter-detail relation, just want DaiCla that respects condition "DaiClaP.ValidTicket=1" and "having count(b.DaiClaID)>0"

  Thanks

 

You are mixing two things here, use the HAVING clause and using IN clause. 

For HAVING clause, as I said, just add the criteria as usual, for example

Where(TLinq.GreaterThan(TProjections.Count(Dic.DaiCla.id.PropName), 0))

that will make Having Count(Id) > 0

As for the IN clause, that is not supported by Aurelius, unfortunately, and in this case you would have to add the whole statement as a raw SQL using SQL expression.

function TBPMMSService.ReadOnHandStockMenuList(
LocationID:int64;
AcYear:integer;
FromDt, ToDt:TDateTime): TList;
var
Criteria: TCriteria;
str: string;
MenuList: TList;
begin

Criteria := TXDataOperationContext.Current.GetManager.Find<TSTOCK_LEDGER>;
if Criteria = Nil then
raise System.Sysutils.Exception.Create( 'Fail to Locate Object TSTOCK_LEDGER' );

Result := Criteria
              .CreateAlias('PRODUCTID', 'P')
              .CreateAlias('LOCATIONID', 'L')
              .CreateAlias('P.PRODUCTCATEGORYID', 'C')
              .Select( TProjections.ProjectionList
                  .Add( Linq['P.PRODUCTID'].Group.As_('PRODUCTID') )
                  .Add( Linq['P.NAME'].Group.As_('PRODUCTNAME') )
                  .Add( Linq['C.PRODUCTCATEGORYID'].Group.As_('CATEGORYID') )
                  .Add( Linq['C.NAME'].Group.As_('CATEGORYNAME') )
                  .Add( Linq['P.CODE'].Group.As_('CODE') )
                  .Add( Linq.Literal<double>(0).Sum.As_('BILLQTY') )
                  .Add( Linq.Literal<double>(0).Sum.As_('RATE') )
                  .Add( Linq['RECEIVEDQTY'].Sum.As_('RECEIVEDQTY') )
                  .Add( Linq['ISSUEQTY'].Sum.As_('ISSUEQTY') )
                  .Add(( Linq['RECEIVEDQTY'] - Linq['ISSUEQTY']).SUM.As_('ONHANDQTY'))
              )
              .Where(
                  (Linq['L.LOCATIONID'] = LocationID) AND
                  (Linq['P.ISACTIVE'] = 1) AND
                  (Linq['P.TYPEID'] = 2) AND
                  (Linq['ACYEAR'] = AcYear) AND
                  (Linq['DT'] >= EncodeDate(YEAROF(FromDt), MONTHOF(FromDt), DAYOF(FromDt))) AND
                  (Linq['DT'] <= EncodeDate(YEAROF(ToDt), MONTHOF(ToDt), DAYOF(ToDt)))  AND
                  ( TLinq.GreaterThan(TProjections.Sum( Linq['RECEIVEDQTY'] - Linq['ISSUEQTY'] ), 0) )
              )
            .OrderBy( 'P.NAME' )
            .List<TMenuList>;

end;

In the Above SQL ONHANDQTY must be more than 0 ie HAVING (SUM(RECEIVEDQTY) - SUM(ISSUEQTY)) > 0.

If I add ( TLinq.GreaterThan(TProjections.Sum( Linq['RECEIVEDQTY'] - Linq['ISSUEQTY'] ), 0) ) the I get Oracle Error NOT GROUP BY.

Please suggest correct way to implement.

Can you please provide the SQL generated by Aurelius in this case?

Result := Criteria
.CreateAlias('INVOICECASHSALES_FList', 'F', TFetchMode.Eager)
.CreateAlias('INVOICECASHSALES_HList', 'H', TFetchMode.Eager)
.CreateAlias('LOCATIONID', 'L', TFetchMode.Eager)
.CreateAlias('L.PARENT_LOCATION_ID', 'LP', TFetchMode.Eager)
.CreateAlias('F.PRODUCTID', 'P', TFetchMode.Eager)
.CreateAlias('H.ORDERTYPE', 'OT', TFetchMode.Eager)
.Select( TProjections.ProjectionList
.Add( Linq['LP.LOCATIONID'].Group.As_('PARENT_LOCATIONID') )
.Add( Linq['LP.NAME'].Group.As_('PARENT_LOCATION') )
.Add( Linq['L.LOCATIONID'].Group.As_('LOCATIONID') )
.Add( Linq['L.NAME'].Group.As_('STORENAME') )
.Add( Linq['L.CITY'].Group.As_('CITY_NAME') )
.Add( Linq['L.STATE'].Group.As_('STATE_NAME') )
.Add( Linq['L.WOKID'].Group.As_('WOKID') )
.Add( Linq['L.LOCCODE'].Group.As_('LOCCODE') )
.Add( TProjections.Condition
( Linq['L.WOKID'].IsNotNull,
Linq['L.WOKID'],
Linq['L.LOCCODE']
).As_('STORE_CODE')
)
.Add( Linq['P.PRODUCTID'].Group.As_('PRODUCTID') )
.Add( Linq['P.NAME'].Group.As_('PRODUCT_NAME') )
.Add( Linq['OT.TYPENAME'].Group.As_('ORDERTYPE') )
.Add( Linq['F.BASICAMT'].Sum.As_('BASICAMT') )
.Add( Linq['F.CASHDISCAMT'].Sum.As_('CASHDISCAMT') )
.Add( (Linq['F.BASICAMT'] - Linq['F.CASHDISCAMT'] - Linq['F.REFUNDAMT'] ).Sum.As_('NETSALES') )
)
.Where (
(Linq['H.TRANHID'].IsNotNull) AND
(Linq['COMPANYID'] = CompanyId) AND
(Linq['TRNTYPEID']._In([8, 408])) AND
(Linq['DOCSTATUSID'] = 1) AND
( (Linq.Sql(sLocFilter)) OR
(Linq['L.LOCATIONID']._In(LocList))
) AND
(Linq.Sql(Filter)) AND
(Linq.Sql<TDateTime, TDateTime>('({DT} BETWEEN ? AND ?)', DateOf(FromDt), DateOf(ToDt))) AND
( TLinq.GreaterThan(TProjections.Sum( Linq['F.BASICAMT'] ), 0) )
)
.OrderBy('LP.LOCATIONID')
.OrderBy('LP.NAME')
.OrderBy('L.LOCATIONID')
.OrderBy('L.NAME')
.OrderBy('P.NAME')
.OrderBy('OT.TYPENAME')
.List;

Generating Following SQL on Oracle
/* Formatted on 20/01/2022 17:07:55 (QP5 v5.360) */
SELECT C.LOCATIONID
AS f0_,
C.NAME
AS f1_,
B.LOCATIONID
AS f2_,
B.NAME
AS f3_,
B.CITY
AS f4_,
B.STATE
AS f5_,
B.WOKID
AS f6_,
B.LOCCODE
AS f7_,
(CASE WHEN B.WOKID IS NOT NULL THEN B.WOKID ELSE B.LOCCODE END)
AS f8_,
E.PRODUCTID
AS f9_,
E.NAME
AS f10_,
G.TYPE
AS f11_,
SUM (D.BASICAMT)
AS f12_,
SUM (D.CASHDISCAMT)
AS f13_,
SUM (((D.BASICAMT - D.CASHDISCAMT) - D.REFUNDAMT))
AS f14_
FROM TRANH A
LEFT JOIN
(LOCATION B
LEFT JOIN
LOCATION C
ON (C.LOCATIONID = B.PARENT_LOCATION_ID))
ON (B.LOCATIONID = A.LOCATIONID)
LEFT JOIN
(INVOICECASHSALES_F D
LEFT JOIN
PRODUCT E
ON (E.PRODUCTID = D.PRODUCTID))
ON (D.TRANHID = A.TRANHID)
LEFT JOIN
(INVOICECASHSALES_H F
LEFT JOIN
POS_ORDERTYPE G
ON (G.ORDERTYPE = F.ORDERTYPE))
ON (F.TRANHID = A.TRANHID)
GROUP BY C.LOCATIONID,
C.NAME,
B.LOCATIONID,
B.NAME,
B.CITY,
B.STATE,
B.WOKID,
B.LOCCODE,
E.PRODUCTID,
E.NAME,
G.TYPE
HAVING ( ( ( ( ( ( ( F.TRANHID IS NOT NULL
AND A.COMPANYID = :p_0)
AND A.TRNTYPEID IN ( :p_1, :p_2))
AND A.DOCSTATUSID = :p_3)
AND ( B.LOCATIONID IS NOT NULL
OR B.LOCATIONID IN ( :p_4)))
AND B.CITY IN ('PUNE'))
AND (A.DT BETWEEN :p_5 AND :p_6))
AND SUM (D.BASICAMT) > :p_7)
ORDER BY C.LOCATIONID ASC,
C.NAME ASC,
B.LOCATIONID ASC,
B.NAME ASC,
E.NAME ASC,
G.TYPE ASC

Entire Where Clause is getting converted into HAVING and that creates NOT GROUP by error on Oracle

Make provision to pass Having Clause separately like .Where() or .OrderBy() eg .Having()

Can you please provide the mapping of all classes involved in the query, so we can reproduce the issue at our side? Or, just send us a project that reproduces the issue. It doesn't need to connect to the database since all we need is the generated SQL from the criteria.

HavingTest.zip (326.7 KB)

Project is big So I added required file. You can refer function TPOSReportService.RepPOSDiscountSummary in POSReportService.

Currently Having Clause is commented which you can uncomment and test. Let me know if any need any help.

You added a big single condition operation with an aggregated field. Aurelius will put everything in the Having clause, that's expected.
To properly expect Aurelius to separate the expressions, you should also do it, so Aurelius knows the different expressions. For example:

              .Where (
                (Linq['H.TRANHID'].IsNotNull) AND
                (Linq['COMPANYID'] = 3)  AND
                (Linq['TRNTYPEID']._In([8, 408])) AND
                (Linq['DOCSTATUSID'] = 1)  AND
                ( (Linq.Sql(sLocFilter)) OR
                     (Linq['L.LOCATIONID']._In(LocList))
                ) AND
//                (Linq.Sql(Filter)) AND
                (Linq.Sql<TDateTime, TDateTime>('({DT} BETWEEN ? AND ?)', DateOf(0), DateOf(0))))
             .Where(
                ( TLinq.GreaterThan(TProjections.Sum( Linq['F.BASICAMT'] ), 0) )
              )

Can I add multiple .Where clause ?

Thanks a lot. Working

1 Like

Yes, that's what I did in my example.

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.