Having Clause

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: 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: Select count(RecId), ItemId From InventTrans Where DateExpected >= 01012006 And Qty > 0 Group By ItemId Haveing 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: 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: 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: 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 ));   } }