Search code examples
sqlaxaptax++dynamics-ax-2009

SQL error when running X++ class in Dynamics AX: The multi-part identifier "D.DATAAREAID" could not be bound

I am getting an SQL error when I try to run my x++ class in Microsoft Dynamics AX. I've researched the issue around this site and google with no luck.

Here is the problematic query:

select custTable
order by custTable.AccountNum
join dirpartytable
join personDetails
outer join address
where custTable.PartyId == dirpartytable.PartyId && //custTable.TableId == dirpartytable.TableId &&
dirPartyTable.FirstName!='' && dirPartyTable.LastName!='' &&
custTable.PartyId == personDetails.PartyId &&
dirpartytable.RecId == address.AddrRecId && dirpartytable.TableId == address.AddrTableId &&
                        custTable.BBBStoreID != '' && custTable.MainContactId != '' && custTable.CRM_AccountNum == ''

And here is the error I am getting:

Error   Message (04:52:10 pm)   Cannot select a record in Customers (CustTable). Customer account: , .
The SQL database has issued an error.

Info    Message (04:52:10 pm)   SQL error description: [Microsoft][SQL Native Client][SQL Server]The multi-part identifier "D.DATAAREAID" could not be bound.

Info    Message (04:52:10 pm)   SQL statement: SELECT A.ACCOUNTNUM,A.NAME,A.ADDRESS,A.PHONE,A.TELEFAX,A.INVOICEACCOUNT,A.CUSTGROUP,A.LINEDISC,A.PAYMTERMID,A.CASHDISC,A.CURRENCY,A.SALESGROUP,A.BLOCKED,A.ONETIMECUSTOMER,A.ACCOUNTSTATEMENT,A.CREDITMAX,A.MANDATORYCREDITLIMIT,A.DIMENSION,A.DIMENSION2_,A.DIMENSION3_,A.VENDACCOUNT,A.TELEX,A.PRICEGROUP,A.MULTILINEDISC,A.ENDDISC,A.VATNUM,A.COUNTRYREGIONID,A.INVENTLOCATION,A.DLVTERM,A.DLVMODE,A.MARKUPGROUP,A.CLEARINGPERIOD,A.ZIPCODE,A.STATE,A.COUNTY,A.URL,A.EMAIL,A.CELLULARPHONE,A.PHONELOCAL,A.FREIGHTZONE,A.CREDITRATING,A.TAXGROUP,A.STATISTICSGROUP,A.PAYMMODE,A.COMMISSIONGROUP,A.BANKACCOUNT,A.PAYMSCHED,A.NAMEALIAS,A.CONTACTPERSONID,A.INVOICEADDRESS,A.OURACCOUNTNUM,A.SALESPOOLID,A.INCLTAX,A.CUSTITEMGROUPID,A.NUMBERSEQUENCEGROUP,A.LANGUAGEID,A.PAYMDAYID,A.LINEOFBUSINESSID,A.DESTINATIONCODEID,A.GIROTYPE,A.SUPPITEMGROUPID,A.GIROTYPEINTERESTNOTE,A.TAXLICENSENUM,A.PAYMSPEC,A.BANKCENTRALBANKPURPOSETEXT,A.BANKCENTRALBANKPURPOSECODE,A.CITY,A.STREET,A.PAGER,A.SMS,A.PACKMATERIALFEELICENSENUM,A.DLVREASON,A.GIROTYPECOLLECTIONLETTER,A.SALESCALENDARID,A.CUSTCLASSIFICATIONID,A.SHIPCARRIERACCOUNT,A.GIROTYPEPROJINVOICE,A.INVENTSITEID,A.ORDERENTRYDEADLINEGROUPID,A.SHIPCARRIERID,A.SHIPCARRIERFUELSURCHARGE,A.SHIPCARRIERBLINDSHIPMENT,A.PARTYTYPE,A.PARTYID,A.SHIPCARRIERACCOUNTCODE,A.PROJPRICEGROUP,A.GIROTYPEFREETEXTINVOICE,A.SYNCENTITYID,A.SYNCVERSION,A.SALESDISTRICTID,A.SEGMENTID,A.SUBSEGMENTID,A.RFIDITEMTAGGING,A.RFIDCASETAGGING,A.RFIDPALLETTAGGING,A.COMPANYCHAINID,A.MAINCONTACTID,A.IDENTIFICATIONNUMBER,A.PARTYCOUNTRY,A.PARTYSTATE,A.GIROTYPEACCOUNTSTATEMENT,A.COMPANYTYPE_MX,A.RFC_MX,A.CURP_MX,A.STATEINSCRIPTION_MX,A.EINVOICE,A.CREDITCARDADDRESSVERIFICATION,A.CREDITCARDCVC,A.CREDITCARDADDRESSVERIFICATI292,A.CREDITCARDADDRESSVERIFICATI293,A.USECASHDISC,A.CASHDISCBASEDAYS,A.USEPURCHREQUEST,A.ANNIVERSARY,A.CHILDRENNAMES,A.MARITALSTATUS,A.PROFESSION,A.BIRTHDATE,A.SPOUSE,A.WEBCUSTOMERID,A.FIRSTNAME,A.LASTNAME,A.MIDDLENAME,A.SPOUSEBIRTHDATE,A.GENDER,A.BBBNEWNAME,A.BBBUPDATENAME,A.BBBSTOREID,A.IGNMAINCONTACTID,A.EMAIL2,A.BBB_EMAIL2,A.CRM_ACCOUNTNUM,A.IGN_FIRSTNAME,A.MODIFIEDDATETIME,A.CREATEDDATETIME,A.RECVERSION,A.RECID,B.GENERATIONALSUFFIX,B.NAME,B.SALUTATION,B.PERSONNAMEORDER,B.LANGUAGEID,B.TYPE,B.PREFIX,B.PROFESSIONALSUFFIX,B.FIRSTNAME,B.MIDDLENAME,B.LASTNAME,B.PARTYID,B.NAMEALIAS,B.MODIFIEDDATETIME,B.MODIFIEDBY,B.CREATEDDATETIME,B.CREATEDBY,B.RECVERSION,B.RECID,C.PARTYID,C.NICKNAME,C.INITIALS,C.CHILDRENNAMES,C.BIRTHDATE,C.MARITALSTATUS,C.ANNIVERSARYDATE,C.HOBBIES,C.GENDER,C.SIPECOMMUNICATIONTYPEID,C.BBB_AGEGROUP,C.BBB_INCOMELEVEL,C.BBB_RINGSIZE,C.BBB_SPOUSERINGSIZE,C.BBB_CLIENTINFORMATION,C.BBB_SIXMONTHFOLLOWUP,C.BBB_PREFERREDCONTACT,C.BBB_SPOUSE_BIRTHDAY,C.BBB_SPOUSE_NAME,C.BBB_DATASOURCE_NAME,C.BBB_DATASOURCE_DATE,C.BBB_JEWELRY_PURCHASER,C.BBB_ZIP_FOUR,C.BBB_CONVERTED_DATE,C.BBB_CONVERTED_DESC,C.BBB_CONVERTED_PROSPECT,C.BBB_IS_VAULT_MEMBER,C.BBB_EM_UNSUBSCRIBE,C.BBB_DM_UNSUBSCRIBE,C.BBB_EM_BOUNCES,C.BBB_DM_BOUNCES,C.BBB_DO_NOT_CALL,C.MODIFIEDDATETIME,C.MODIFIEDBY,C.CREATEDDATETIME,C.CREATEDBY,C.RECVERSION,C.RECID,D.ADDRTABLEID,D.ADDRRECID,D.LINENUM,D.TYPE,D.NAME,D.ADDRESS,D.PHONE,D.TELEFAX,D.COUNTRYREGIONID,D.ZIPCODE,D.STATE,D.COUNTY,D.TELEX,D.URL,D.PHONELOCAL,D.CELLULARPHONE,D.EMAIL,D.TAXGROUP,D.CITY,D.STREET,D.PAGER,D.SMS,D.LATITUDE,D.LONGITUDE,D.SALESCALENDARID,D.TIMEZONE,D.DLVTERM,D.DLVMODE,D.SHIPCARRIERID,D.SHIPCARRIERBLINDSHIPMENT,D.SHIPCARRIERACCOUNT,D.SHIPCARRIERACCOUNTCODE,D.SHIPCARRIERRESIDENTIAL,D.BBB_ZIP4,D.RECVERSION,D.RECID,A.MEMO,B.MEMO FROM  {oj CUSTTABLE A LEFT OUTER JOIN DIRPARTYTABLE B ON ((D.DATAAREAID=?) AND (((((((((A.PARTYID=B.PARTYID) AND (B.FIRSTNAME<>?)) AND (B.LASTNAME<>?)) AND (A.PARTYID=C.PARTYID)) AND (B.RECID=D.ADDRRECID)) AND (D.ADDRTABLEID=?)) AND (A.BBBSTOREID<>?)) AND (A.MAINCONTACTID<>?)) AND (A.CRM_ACCOUNTNUM=?)))},DIRPARTYTABLE B,DIRPERSONPARTYDETAIL C WHERE (A.DATAAREAID=?) AND (B.DATAAREAID=?) AND (C.DATAAREAID=?) ORDER BY A.DATAAREAID,A.ACCOUNTNUM

Any suggestions or ideas would be greatly appreciated!

Solution

  • What happens here is that the generated query doesn't include all your tables. The reason for this is weirdness in AX when combining inner and outer joins in the same query.

    See this blog post

    The solution would be to convert the inner join to an outer join, which will off course give you wrong results as it will return your records even if it doesn't have a matching dirpartytable or persondetails row.

    If you can have customers without a partytable or persondetails row you would need to filter in your code.

    Also, if you use all outer joins you need to position your where clauses more carefully.

    Some examples:

    //Works
select custTable
order by custTable.AccountNum
outer join dirpartytable
where custTable.PartyId == dirpartytable.PartyId
outer join address
// && //custTable.TableId == dirpartytable.TableId &&
where dirPartyTable.FirstName!='' && dirPartyTable.LastName!='' &&
dirpartytable.RecId == address.AddrRecId && dirpartytable.TableId == address.AddrTableId &&
                           custTable.MainContactId != '' ;//&& custTable.CRM_AccountNum == ''

//Works    
select custTable
order by custTable.AccountNum
join dirpartytable
join address
where custTable.PartyId == dirpartytable.PartyId && //custTable.TableId == dirpartytable.TableId &&
dirPartyTable.FirstName!='' && dirPartyTable.LastName!='' &&
dirpartytable.RecId == address.AddrRecId && dirpartytable.TableId == address.AddrTableId &&
                           custTable.MainContactId != '' ;//&& custTable.CRM_AccountNum == ''

//Doesnt work
select custTable
order by custTable.AccountNum
join dirpartytable
where custTable.PartyId == dirpartytable.PartyId
outer join address
// && //custTable.TableId == dirpartytable.TableId &&
where dirPartyTable.FirstName!='' && dirPartyTable.LastName!='' &&
dirpartytable.RecId == address.AddrRecId && dirpartytable.TableId == address.AddrTableId &&
                           custTable.MainContactId != '' ;//&& custTable.CRM_AccountNum == ''


//Doesnt work    
select custTable
order by custTable.AccountNum
join dirpartytable
outer join address
where custTable.PartyId == dirpartytable.PartyId && //custTable.TableId == dirpartytable.TableId &&
dirPartyTable.FirstName!='' && dirPartyTable.LastName!='' &&
dirpartytable.RecId == address.AddrRecId && dirpartytable.TableId == address.AddrTableId &&
                           custTable.MainContactId != '' ;//&& custTable.CRM_AccountNum == ''