Job Help

jackson1306

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


}

kranthi

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;

jackson1306

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

Which version of AX it is?
Use an Exists Join...