Job Help

jackson1306
jackson1306 Member Posts: 5
edited 2011-06-21 in Dynamics AX
Hi can anyone help in essense I need to join two tables based on a key field.

Once I have done this I then need to say if field A in table A = value && Field B in Table B = value then delete record but my job doesn't compile. Please could someone point me in the right direction ?.

Kind Regards


Andrew


static void DataCleanse(Args _args)
{


OCSSalesOrderHold OCSSalesOrderHold;
SalesTable SalesTable;
;
while select SalesTable
join OCSSalesOrderHold
where OCSSalesOrderHold.SalesId==SalesTable.SalesId
delete_from OCSSalesOrderHold
where SalesTable.DocumentStatus == SalesTable.DocumentStatus ::Invoice
&& OCSSalesOrderHold.Status ==OCSSalesOrderHold.Status ::Held


}

Comments

  • kranthi
    kranthi Member Posts: 72
    Remove the while select statement and use join in the delete_from statement.
    like this

    ttsbegin;
    delete_from OCSSalesOrderHold
    join sales Table
    where salesTable.SaLesId == OCSSalesOrderHold.SalesId
    && SalesTable.DocumentStatus == DocumentStatus::Invoice
    && OCSSalesOrderHold.Status == Status ::Held;///Use the enumType here(EnumName::Element)
    ttscommit;
    Kranthi
  • jackson1306
    jackson1306 Member Posts: 5
    When I run this query I get error 134 inner or outer join is illegal in this context.

    Any ideas please ?.

    Regards

    A

    Query So Far:

    static void DataCleanse(Args _args)
    {


    OCSSalesOrderHold OCSSalesOrderHold;
    SalesTable SalesTable;
    ;

    ttsbegin;
    delete_from OCSSalesOrderHold
    join SalesTable
    where SalesTable.SalesId == OCSSalesOrderHold.SalesId
    && SalesTable.DocumentStatus == DocumentStatus::Invoice
    && OCSSalesOrderHold.Status == Status ::Held;
    ttscommit

    }
  • kranthi
    kranthi Member Posts: 72
    Which version of AX it is?
    Use an Exists Join...
    Kranthi

Categories