troubleshooting Question

SQL -Multi-part identifier can't be found.

Avatar of Zack
ZackFlag for Australia asked on
Microsoft SQL ServerSQL
5 Comments1 Solution12 ViewsLast Modified:
Hi EE,

I have the following query:

 SELECT     VENDPACKINGSLIPJOUR.ORDERACCOUNT, DIRPARTYTABLE.NAME, VENDPACKINGSLIPTRANS.ITEMID, VENDPACKINGSLIPTRANS.NAME AS ItemDescr,
                      VENDPACKINGSLIPTRANS.PURCHUNIT, SUM(VENDPACKINGSLIPTRANS.QTY) AS Aantal, SUM(VENDPACKINGSLIPTRANS.VALUEMST) AS Bedrag,
                      INVENTDIM.INVENTSIZEID, INVENTDIM.INVENTCOLORID
FROM         (VENDPACKINGSLIPJOUR INNER JOIN VENDPACKINGSLIPTRANS ON VENDPACKINGSLIPJOUR.PACKINGSLIPID = VENDPACKINGSLIPTRANS.PACKINGSLIPID
  INNER JOIN VENDTABLE ON VENDPACKINGSLIPJOUR.ORDERACCOUNT = VENDTABLE.ACCOUNTNUM AND
                  DIRPARTYTABLE.RECID = VENDTABLE.PARTY AND
                      VENDPACKINGSLIPTRANS.DATAAREAID = VENDTABLE.DATAAREAID AND
                      VENDPACKINGSLIPJOUR.DATAAREAID = VENDTABLE.DATAAREAID INNER JOIN
                      PURCHTABLE ON VENDTABLE.DATAAREAID = PURCHTABLE.DATAAREAID AND
                      VENDPACKINGSLIPJOUR.PURCHID = PURCHTABLE.PURCHID INNER JOIN
                      PURCHLINE ON PURCHTABLE.PURCHID = PURCHLINE.PURCHID AND VENDTABLE.DATAAREAID = PURCHLINE.DATAAREAID AND
                      VENDPACKINGSLIPJOUR.PURCHID = PURCHLINE.PURCHID INNER JOIN
                      INVENTDIM ON PURCHLINE.INVENTDIMID = INVENTDIM.INVENTDIMID AND VENDTABLE.DATAAREAID = INVENTDIM.DATAAREAID
WHERE     VENDPACKINGSLIPTRANS.DELIVERYDATE >= '2019-01-01' AND VENDPACKINGSLIPTRANS.DELIVERYDATE <= '2019-12-01'
GROUP BY VENDPACKINGSLIPTRANS.ITEMID, VENDTABLE.NAME, VENDPACKINGSLIPTRANS.NAME, VENDTABLE.DATAAREAID,
                      VENDPACKINGSLIPJOUR.ORDERACCOUNT, VENDPACKINGSLIPTRANS.PURCHUNIT, INVENTDIM.INVENTSIZEID, INVENTDIM.INVENTCOLORID

I am trying to include the   DIRPARTYTABLE.RECID = VENDTABLE.PARTY into the query but it's giving an error multi-part identifier can't be found.

Below is a query that I have managed to incorporate the  DIRPARTYTABLE.RECID = VENDTABLE.PARTY into successfully:

              SELECT distinct VENDTABLE.DATAAREAID ENTITY, VENDTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME,Address.ADDRESS ,
 VENDTABLE.CURRENCY ,VENDTABLE.VENDGROUP ,
 VENDTABLE.PAYMTERMID,VENDTABLE.TAXGROUP VATGROUP ,VENDTABLE.CASHDISC ,
 VendBankAccount.ACCOUNTID BankAccount ,VendBankAccount.NAME 'Bank Name' ,VendBankAccount.ACCOUNTNUM 'Bank account number',
 VendBankAccount.RegistrationNum 'Routing Number',VendBankAccount.SWIFTNo,VendBankAccount.BankIBAN
 from VENDTABLE left outer JOIN VendBankAccount ON VendBankAccount.VENDACCOUNT = VENDTABLE.ACCOUNTNUM --AND  VendBankAccount.ACCOUNTID = VENDTABLE.BANKACCOUNT
 left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = VENDTABLE.PARTY
 left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = DIRPARTYTABLE.PRIMARYADDRESSLOCATION
  WHERE VENDTABLE.DATAAREAID IN ('EF') --and VENDTABLE.ACCOUNTNUM ='test033'
 Order by  VENDTABLE.DATAAREAID,VENDTABLE.ACCOUNTNUM

How can join the DIRPARTYTABLE.RECID = VENDTABLE.PARTY relationship into the first query?

Any assistance is appreciated.

Thank you

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros