CODEWITHSUNDEEP
sql-serveraxaptax++
Craig
Craig

Reputation: 329

Microsoft Dynamics AX Outer Join different than equivalent SQL

I have the below x++ method:

public void run()
{
    CustTable           ct;
    DirPartyTable       dir;
    DirAddressBookParty party;
    DirAddressBook      ab;
    int                 ctr = 0;
    ;  

     while
       select * from ct
         where ct.UT_AP_CustomerStatus == 1
       outer join * from dir
         where dir.RecId == ct.Party 
            && dir.Partition == ct.Partition
       outer join * from party
         where ct.Party == party.Party
       outer join * from ab
         where party.AddressBook == ab.RecId
            && ab.Name == ''
    {
        ctr++;
    }

    info(strFmt('Finished with %1 rows selected',ctr));
}

When this runs, it ALWAYS returns 2,346 rows, which is how many rows are in CUSTTABLE. However, when I run the below SQL in SSMS, it ONLY returns one row, like it should!

SELECT *
  FROM [DYNAMICS_AX].[dbo].[CUSTTABLE] ct
       LEFT OUTER JOIN [DYNAMICS_AX].[dbo].[DIRPARTYTABLE] dir
                    ON dir.RECID = ct.PARTY
                   AND dir.PARTITION = ct.PARTITION
       LEFT OUTER JOIN [DYNAMICS_AX].[dbo].[DIRADDRESSBOOKPARTY] party
                    ON ct.PARTY = party.PARTY
       LEFT OUTER JOIN [DYNAMICS_AX].[dbo].[DIRADDRESSBOOK] ab
                    ON party.ADDRESSBOOK = ab.RECID
 WHERE ct.UT_AP_CUSTOMERSTATUS = 1
   AND ab.NAME IS NULL

Can anyone see what's wrong with the x++ query vs. my SQL statement?

Thanks, Craig

With all of your great help/suggestions, I realized what needed to be changed. First off, the reason my SQL was returning one row with the ab.NAME is null, is because the left joins that didn't find anything was putting NULLS into the result set. That and showing me how to look up the SQL that x++ generates really helped as well, and I was able to change my x++ to below to give me the single row I was wanting to update:

while
     select ct
        where ct.UT_AP_CustomerStatus == 1
        notExists join party from party
        where ct.Party == party.Party

Thanks for your help!

Upvotes: 0

Views: 1782

Answers (2)

Jan B. Kjeldsen
Jan B. Kjeldsen

Reputation: 18051

In AX I would expect your query to give you the customeres where UT_AP_CustomerStatus == 1.

The ab.Name == '' does not change the count as the AX query just selects the DirAddressBook with blank names. It is translated to an ON clause in SQL. If you really want to use outer join and have this range go to where clause, then you need to create a query and use query filters, that is use addQueryFilter.

Not knowing what you really want to get, but you could try to strip the outer using a plain inner join. Also, you never want to select on Partition or DataAreaId as the system does that for you.

Formatted with best practice in mind:

public void run()
{
    CustTable           ct;
    DirPartyTable       dir;
    DirAddressBookParty party;
    DirAddressBook      ab;
    int                 ctr = 0;
    while select ct
        where ct.UT_AP_CustomerStatus == 1
        join RecId from dir
        where dir.RecId == ct.Party
        join RecId from party
        where ct.Party == party.Party
        join RecId from ab
        where ab.RecId == party.AddressBook
           && ab.Name == ''
    {
        ctr++;
    }

    info(strFmt('Finished with %1 rows selected',ctr));
}

Upvotes: 1

Matej
Matej

Reputation: 7627

Difference between you r X++ and SQL statement is in ab.Name == '' and ab.NAME IS NULL.

Another difference is that where condition should be written in JOIN ... ON part:

 LEFT OUTER JOIN [DYNAMICS_AX].[dbo].[DIRADDRESSBOOK] ab
   ON party.ADDRESSBOOK = ab.RECID
     AND ab.NAME = '' -- <-- ab.Name condition in JOIN part
 WHERE ct.UT_AP_CUSTOMERSTATUS = 1

You can see exact SQL statement from AX x++ to SQL with Trace Parser.

Another option is to use generateOnly (dev/research only):

select generateonly * from custTable;
info(custTable.getSQLStatement());

Upvotes: 2

Related Questions