web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Microsoft Dynamics AX (Archived)

Microsoft Dynamics AX Outer Join different than equivalent SQL

(0) ShareShare
ReportReport
Posted on by
I have this 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

I'm fairly new at x++, so be gentle!  Can anyone see what's wrong with the x++ query vs. my SQL statement?

Thanks, Craig




*This post is locked for comments

I have the same question (0)
  • chessdr Profile Picture
    on at
    RE: Microsoft Dynamics AX Outer Join different than equivalent SQL

    I thought to do a LEFT OUTER JOIN in x++ that you had to use the word OUTER?  If I change my SQL and remove the LEFT OUTER statements, no row(s) are returned.  Also, I'm ultimately trying to return any rows from the DIRADDRESSBOOK where the NAME is null and they are ACTIVE customers.  

  • chessdr Profile Picture
    on at
    RE: Microsoft Dynamics AX Outer Join different than equivalent SQL

    A person has suggested the following:

    while

            select *

             from ct

              outer join dir

              outer join party

              outer join ab

              where ct.UT_AP_CustomerStatus == 1

                 && dir.RecId == ct.Party

                 && dir.Partition == ct.Partition

                 && ct.Party == party.Party

                 && party.AddressBook == ab.RecId

                 && ab.Name == ''

              {

                ctr++;

              }

    But this query never seems to return!  In my SQL statement, even if I remove the CustomerStatus and ab.Name where clause, there are only 9,856 rows being returned. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#3
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans