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.
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
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.