Unified Operations & Dynamics AX Forum

Expand all | Collapse all

Query on top of a view getting zero value

  • 1.  Query on top of a view getting zero value

    Posted 10 days ago
    I am testing below code and have noted that X++ is not getting the value form the DB, X++ is getting zero for field tmpTable.QtyTransfer while if i check the sql being executed it  returns not zero values.
    Could you please check if there is something wrong in my code? Are views not suppose to be used for aggregations?

    public void updateQueryForMenu()
    {
    QueryBuildDataSource qbds;
    QueryBuildRange qbr;

    date fromDate = systemDateGet() - 60;
    date toDate = systemDateGet();


    ANGTransferOrders tmpTable;
    tmpTransferOrders tmpTableT;
    QueryRun queryRun;

    queryDP = new Query();
    qbds = queryDP.addDataSource(tableNum(ANGTransferOrders));
    qbds.addGroupByField(fieldNum(ANGTransferOrders, ItemId));
    qbds.addGroupByField(fieldNum(ANGTransferOrders, ProductName));
    qbds.addGroupByField(fieldNum(ANGTransferOrders, configId));

    qbds = queryDP.dataSourceNo(1);

    qbds.addSelectionField(fieldNum(ANGTransferOrders, ItemId));
    qbds.addSelectionField(fieldNum(ANGTransferOrders, ProductName));
    qbds.addSelectionField(fieldNum(ANGTransferOrders, configId));
    qbds.addSelectionField(fieldNum(ANGTransferOrders, QtyTransfer), SelectionField::Sum);
    qbds.orderMode(OrderMode::GroupBy);


    qbr = qbds.addRange(fieldNum(ANGTransferOrders, ShipDate));
    qbr.value(queryRange(fromDate, toDate));

    qbr = qbds.addRange(fieldNum(ANGTransferOrders, InventLocationIdFrom));
    qbr.value("@ANG773");
    qbr.status(RangeStatus::Locked);

    qbr = qbds.addRange(fieldNum(ANGTransferOrders, InventLocationIdTo));
    qbr.value("@ANG774");
    qbr.status(RangeStatus::Locked);

    qbr = qbds.addRange(fieldNum(ANGTransferOrders, TransferStatus));
    qbr.value(queryRange(InventTransferStatus::Shipped,InventTransferStatus::Received));
    qbr.status(RangeStatus::Locked);


    queryRun = new QueryRun(queryDP);

    if(queryRun.prompt())
    {
    while (queryRun.next())
    {
    tmpTable = queryRun.get(tableNum(ANGTransferOrders));
    tmpTableT.ItemId = tmpTable.ItemId;
    tmpTableT.ProductName = tmpTable.ProductName;
    tmpTableT.ShipQty = tmpTable.QtyTransfer; /// Returning Zero while Running query on SQL is not.

    tmpTableT.insert();
    }

    tmpTransferOrders.setTmpData(tmpTableT);
    }
    else
    {
    element.close();
    }

    tmpTransferOrders.setTmpData(tmpTableT);
    }


    **************************************************
    Query sent to DB    ********** Not alias for the field.
    **************************************************
    NAME: queryDPNAME: queryDP
    VALUE: Query OBJECT 1
    b6db460:
    SELECT ItemId ,ProductName ,configId ,SUM(QtyTransfer)
    FROM ANGTransferOrders(ANGTransferOrders_1)GROUP BY ANGTransferOrders.ItemId ,ANGTransferOrders.ProductName ,ANGTransferOrders.configIdWHERE ( ( ShipDate >= {ts '2016-12-09 00:00:00.000' } AND ShipDate <= {ts '2017-02-07 00:00:00.000' } ) ) AND ((InventLocationIdFrom = N'TJ-MAIN')) AND ((InventLocationIdTo = N'SD-MAIN')) AND ( ( TransferStatus >= 1 AND TransferStatus <= 2 ) )
    TYPE: Query




    ------------------------------
    Francio Maestre
    Francio Guerra
    tijuana
    ------------------------------


  • 2.  RE: Query on top of a view getting zero value

    Posted 10 days ago
    Edited by Moeen Ahmed Sultan 10 days ago
    Hi,

    You are using SUM(QtyTransfer) without using GROUP BY clause in the query. Use GROUP BY or don't sum up the QtyTransfer.

    Regards,
    Moeen Ahmed Sultan