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: <xpp> class My_FieldId implements SysPackable {

   TableId     tableId;
   FieldId     fieldId;
   Name        dsName;

    #define.CurrentVersion(1)     #localmacro.CurrentList         tableId,

       fieldId,
       dsName

    #endmacro } </xpp> <xpp> void new(

   TableId _tableId    = 0,
   FieldId _fieldId    = 0,
   Name    _dsName     = 
   )

{

   ;

   tableId = _tableId;
   fieldId = _fieldId;
   dsName  = _dsName;

} </xpp> <xpp> public container pack() {     ;       return [#CurrentVersion,#CurrentList]; } </xpp> <xpp> public boolean unpack(container _packedClass) {     int         version = runbase::getVersion(_packedClass);       switch (version)     {         case #CurrentVersion:             [version,#CurrentList] = _packedClass;             return true;         default :             return false;     }       return false; } </xpp> <xpp> TableId tableId(TableId _tableId = tableId) {

   ;
   tableId = _tableId;
   return tableId;

} </xpp> <xpp> FieldId fieldId(FieldId _fieldId = fieldId) {

   ;
   fieldId = _fieldId;
   return fieldId;

} </xpp> <xpp> Name dsName(Name _dsName = dsName) {

   ;
   dsName = _dsName;
   return dsName;

} </xpp> <xpp> str toString() {

   return strfmt("%1;%2;%3", tableId, fieldId, dsName);

} </xpp>

Now comes a new Static Method for the SysQuery Class: <xpp> 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) : );

} </xpp>

And now an Example of How to Use it: <xpp> 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
           ));
   }

} </xpp>