Illegal use of WHERE Expression in ax 2012

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.

&& _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.

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)

Hi Kranthi,

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

Thanks again.

both method ?

Yes. Also remove the group by Item Id.