Illegal use of WHERE Expression in ax 2012

Dynamics 365 for Finance and Operations/AX Developer Forum
#1

Hi Guys,

Can you please help me with my code.

here is the method with error. (Line 15)

public real getLastPurchasePrice(
        ItemId  _itemId)
{
    real                                        _Lpp;

    InventTrans                                 _inventTrans;
    InventTransOrigin                           _inventTransOrigin;


     while select sum(CostAmountPosted), sum(Qty) from _inventTrans
        group by ItemId
        where   _inventTrans.ItemId                                 == _itemId
            &&  _inventTrans.StatusReceipt                          == StatusReceipt::Purchased
            &&  _inventTrans.dataAreaId                             == 'wiz'
            &&  _inventTrans.DatePhysical                           == this.getLatest(_inventTrans.ItemId)
         

        join    _inventTransOrigin
        where   _inventTransOrigin.RecId                            == _inventTrans.InventTransOrigin
            &&  _inventTransOrigin.dataAreaId                       == 'wiz'
            &&  _inventTransOrigin.ReferenceCategory                == InventTransType::Purch

    {
        _Lpp =  minOne(_inventTrans.CostAmountPosted) / minOne(_inventTrans.Qty);
    }

    return _Lpp;
}

and this is the method that i call…

public DatePhysical getLatest(
    InventTrans  _item)
{
      DatePhysical                _datePhysical;

   // ItemId              _itemId = '0000022928';
    InventTrans         _inventTrans;
    InventTransOrigin   _inventTransOrgin;


 while  select maxof(DatePhysical) from _inventTrans
    where       _inventTrans.ItemId                     == _item.ItemId
            && _inventTrans.dataAreaId                  == 'wiz'
            && _inventTrans.StatusReceipt               == StatusReceipt::Purchased

     join _inventTransOrgin
        where   _inventTrans.InventTransOrigin          == _inventTransOrgin.RecId
            &&  _inventTransOrgin.dataAreaId            == 'wiz'
            &&  _inventTransOrgin.ReferenceCategory     == InventTransType::Purch
    {
        _datePhysical = _inventTrans.DatePhysical;
    }
    
    return _datePhysical;

    
}

please help me…

thank you.

#2

&& _inventTrans.DatePhysical == this.getLatest(_inventTrans.ItemId)
You cannot call that method in the where clause .
Create a variable and assign the value returned by that method to that variable(before while) and use the variable in where clause.

#3

You may also change the below statement to exists join as you are not using the table data anywhere.
join _inventTransOrigin
where _inventTransOrigin.RecId == _inventTrans.InventTransOrigin

Also remove the while in getLatest (you simply use select statement)

#4

Hi Kranthi,

Thanks for quick response.
i will give you feedback later…

Thanks again.

#5

both method ?

#6

Yes. Also remove the group by Item Id.