Reputation: 11564
We merged a few hundred duplicate customer accounts that had transactions. Customer "Alexander Smith" and "Alex Smith" for example were merged into "Alexander Smith". In our custTableMergeLog
, I inserted a record of this merge with an old/new cust account.
This query should merely identify ledgerJournalTrans records that have an invalid customer, but we have a record that it was merged.
It doesn't work when I uncomment the two lines...any ideas?
while select ledgerJournalTrans
where ledgerJournalTrans.AccountType == LedgerJournalACType::Cust
notexists join custTable
where custTable.AccountNum == ledgerJournalTrans.AccountNum
//exists join custTableMergeLog
// where custTableMergeLog.CustAccountOld == ledgerJournalTrans.AccountNum
{
select firstonly custTableMergeLog2
where custTableMergeLog2.CustAccountOld == ledgerJournalTrans.AccountNum;
info(strfmt("Account# %1, CustTable Name: %2, CustMergeLog %3", ledgerJournalTrans.AccountNum, CustTable::find(ledgerJournalTrans.AccountNum).Name, custTableMergeLog2.CustAccountOld));
i++;
if (i>10)
break;
}
Upvotes: 0
Views: 358
Reputation: 783
The conversion from X++ queries to real SQL does not always work as intended, and I bet you have one of those examples where it does not work.
What if you start your query in custTableMergeLog, then join ledgerJournalTrans and finally custTable?
Sort of like this:
while select custTableMergeLog
join ledgerJournalTrans
where custTableMergeLog.CustAccountOld == ledgerJournalTrans.AccountNum
notexists join custTable
where custTable.AccountNum == ledgerJournalTrans.AccountNum
Will that work for you?
Upvotes: 1