Источник:
http://www.axaptapedia.com/Having_Clause
==============
Summary:
The Having Clause is a part of an SQL Statemet which places a condition on an Aggregate Element.
If for Instance you execute a select statement which looks like this:
X++:
Select count(RecId), ItemId
From InventTrans
Where DateExpected >= 01012006
And Qty > 0
Group By ItemId;The result set would show the number of records grouped by ItemId from the InventTrans Table
whith the restrictions that the DateExpected Field must have a Date greater or Equal to the 1st of January 2006
and the Quantity Field must have a Value greater than zero.
If you wish to additionally restrict the selection so that only records
with a count greater than one are selected, then the Having Clause must be used:
X++:
Select count(RecId), ItemId
From InventTrans
Where DateExpected >= 01012006
And Qty > 0
Group By ItemId
Having count(RecId) > 1;
Neat huh !?
The only problem is this won't work in Axapta, because the Having Clause is not supported :-(
Well... It's a little complicated, but I've given it a shot and maybe this solution will help.
It will only work with Queries.
Here's the Code:
First you need to build a small class for FieldId information:
X++:
class My_FieldId implements SysPackable
{
TableId tableId;
FieldId fieldId;
Name dsName;
#define.CurrentVersion(1)
#localmacro.CurrentList
tableId,
fieldId,
dsName
#endmacro
}
void new(
TableId _tableId = 0,
FieldId _fieldId = 0,
Name _dsName = ''
)
{
;
tableId = _tableId;
fieldId = _fieldId;
dsName = _dsName;
}
public container pack()
{
;
return [#CurrentVersion,#CurrentList];
}
public boolean unpack(container _packedClass)
{
int version = runbase::getVersion(_packedClass);
switch (version)
{
case #CurrentVersion:
[version,#CurrentList] = _packedClass;
return true;
default :
return false;
}
return false;
}
TableId tableId(TableId _tableId = tableId)
{
;
tableId = _tableId;
return tableId;
}
FieldId fieldId(FieldId _fieldId = fieldId)
{
;
fieldId = _fieldId;
return fieldId;
}
Name dsName(Name _dsName = dsName)
{
;
dsName = _dsName;
return dsName;
}
str toString()
{
return strfmt("%1;%2;%3", tableId, fieldId, dsName);
}Now comes a new Static Method for the SysQuery Class:
X++:
public static str My_HavingList(
Query _query,
QueryBuildDataSource _qbds,
List _groupByFields,
FieldId _evaluationField,
AnyType _value,
CuesThresholdCriteria _criteria
)
{
Queryrun queryRun;
My_FieldId qgbf;
TableId tableId;
FieldId fieldId;
TableId gb_tableId;
FieldId gb_fieldId;
Common common;
Map mapRecords;
AnyType value;
str txt;
str ret;
str condition;
int i;
boolean test()
{
;
switch (_criteria)
{
case CuesThresholdCriteria::Equals :
return value == _value;
case CuesThresholdCriteria::NotEquals :
return value != _value;
case CuesThresholdCriteria::LessThan :
return value < _value;
case CuesThresholdCriteria::LessThanOrEqual :
return value <= _value;
case CuesThresholdCriteria::GreaterThan :
return value > _value;
case CuesThresholdCriteria::GreaterThanOrEqual :
return value >= _value;
default :
return false;
}
}
str buildCondition()
{
ListEnumerator le = _groupByFields.getEnumerator();
Name dsName;
Common lCommon;
str lret;
str lValue;
int lCnt;
;
while (le.moveNext())
{
qgbf = new My_FieldId();
qgbf.unpack(le.current());
gb_tableId = qgbf.tableId();
gb_fieldId = qgbf.fieldId();
dsName = qgbf.dsName();
if (dsName)
lCommon = mapRecords.lookup(dsName);
else
lCommon = queryRun.get(gb_tableId);
if (lCommon)
{
if (new DictField(lCommon.TableId, gb_fieldId).baseType() == Types::String)
lValue = strfmt('(%1.%2 == "%3")',
dsName,
fieldid2name(gb_tableId, gb_fieldId),
queryValue(lCommon.(gb_fieldId)));
else
lValue = strfmt('(%1.%2 == %3)',
dsName,
fieldid2name(gb_tableId, gb_fieldId),
lCommon.(gb_fieldId));
lret += (lret ? ' && ' : '') + lValue;
lCnt++;
}
}
return (lCnt > 1 ? '(' + lret + ')' : lCnt == 1 ? lret : '');
}
;
if(_query && _groupByFields && _value && _criteria)
{
txt = _query.dataSourceNo(1).toString();
tableId = _qbds.table();
if (tableId && _evaluationField)
{
queryRun = new QueryRun(_query);
while (queryRun.next())
{
mapRecords = new Map(Types::String, Types::Record);
for (i = 1; i <= _query.dataSourceCount(); i++)
{
common = queryRun.get(_query.dataSourceNo(i).table());
mapRecords.insert(_query.dataSourceNo(i).name(), common);
}
common = queryRun.get(tableId);
value = common.(_evaluationField);
if (test())
{
condition = strfmt('%1', buildCondition());
if (condition)
{
ret += (ret ? ' || ' : '') + condition;
}
}
}
}
}
return (ret ? strfmt('(%1)', ret) : '');
}And now an Example of How to Use it:
X++:
static void TestJob_HavingClause(Args _args)
{
Query query = new Query();
QueryRun qr;
QueryBuildDataSource dsSalesTable;
QueryBuildFieldList qbflSalesTable;
SalesTable salesTable;
My_FieldId qgbf1;
My_FieldId qgbf2;
My_FieldId qgbf3;
QueryBuildRange range1;
str rangeValue;
;
dsSalesTable = query.addDataSource(tableNum(SalesTable), identifierstr(SalesTable));
qbflSalesTable = dsSalesTable.fields();
qbflSalesTable.clearFieldList();
qbflSalesTable.addField (fieldNum(SalesTable, RecId), SelectionField::Count);
dsSalesTable.orderMode(OrderMode::GroupBy);
dsSalesTable.addSortField (fieldNum(SalesTable, CustAccount));
dsSalesTable.addSortField (fieldNum(SalesTable, InvoiceAccount));
dsSalesTable.addSortField (fieldNum(SalesTable, SalesResponsible));
qgbf1 = new My_FieldId(tablenum(SalesTable), fieldNum(SalesTable, CustAccount), identifierstr(SalesTable));
qgbf2 = new My_FieldId(tablenum(SalesTable), fieldNum(SalesTable, InvoiceAccount), identifierstr(SalesTable));
qgbf3 = new My_FieldId(tablenum(SalesTable), fieldNum(SalesTable, SalesResponsible), identifierstr(SalesTable));
range1 = dsSalesTable.addRange(fieldNum(SalesTable, DataAreaId));
rangeValue = SysQuery::My_HavingList(
query, // The Query
dsSalesTable, // The DataSource where the Aggregate Function Field comes from
con2list([qgbf1.pack(), // A List of packed Field Information of Fields which are not Aggregate
qgbf2.pack(),
qgbf3.pack()]),
fieldNum(SalesTable, RecId), // The FieldId of the Aggreagate Field
1, // The Value for the Having to be evaluated
CuesThresholdCriteria::GreaterThan); // The Enumarator which define the evaluation
range1.value(rangeValue);
qr = new QueryRun(query);
while (qr.next())
{
salesTable = qr.get(tablenum(SalesTable));
info (strfmt("%1;%2;%3;%4",
salesTable.RecId,
salesTable.CustAccount,
salesTable.InvoiceAccount,
salesTable.SalesResponsible
));
}
}