axaptapedia: Having Clause

[Blog bot]

Источник: 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:

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

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
            ));
    }
}

[mazzy]

прикольно!

но это, конечно, не спортивно.
переносить выполнение having из SQL'я в Аксапту, прогоняя запрос два раза...
Но хоть так. Может и поможет при повторных использованиях...