Why doesn't my AX SQL work properly

Question

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;
}

score 1 · Answer 1 · answered Jan 10 '13 at 10:37

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?

Why doesn't my AX SQL work properly

1 Answers1