Click here to Skip to main content
14,391,232 members

Multi-part identifier could not be bound

harish kashyap01 asked:

Open original thread
Hi I am Tryinfg to get REPORTS FROM Multiple tables.I added column in select statement.
But I am getting the following error.



"The multi-part identifier "UR_PurchaseHeaders.SupplierReferenceNo" could not be bound."

What I have tried:

SELECT COM_StateMaster.State,COM_StateMaster.StateCode,HeaderStateIDOfSupply AS StateID,OutSideState,'Inward' AS TaxationType,'Purchase' AS TransactionType,COALESCE(PUR_PurchaseHeaders.ReferenceNo, CONVERT(nvarchar,PurchaseInvoiceNo)) AS VoucherNo,InvoiceDate AS VoucherDate,
VU_PUR_Suppliers.Supplier AS PartyName,VU_PUR_Suppliers.GSTNo AS GSTNo,VU_PUR_Suppliers.StateID AS PartyStateID,VU_PUR_Suppliers.State AS PartyState,VU_PUR_Suppliers.BusinessType,VU_PUR_Suppliers.StateCode AS PartyStateCode,
PUR_PurchaseHeaderLines.ProductCode,INV_Products.HSNCode,ISNULL(INV_MeasurementUnits.UQC,INV_Products.UnitCode) AS UnitCode,PUR_PurchaseHeaderLines.Quantity,ExtendedPrice AS InvoiceValue,LinePrice AS TaxableAmount,PUR_PurchaseHeaders.SupplierReferenceNo AS SupplierReferenceNo CASE WHEN TaxGLCode1=2490 THEN Tax1Rate WHEN TaxGLCode2=2490 THEN Tax2Rate WHEN TaxGLCode3=2490 THEN Tax3Rate END AS CGSTPercentage,
CASE WHEN TaxGLCode1=2500 THEN Tax1Rate WHEN TaxGLCode2=2500 THEN Tax2Rate WHEN TaxGLCode3=2500 THEN Tax3Rate END AS SGSTPercentage CASE WHEN (ISNULL(LineTax1,0)<=0 AND ISNULL(LineTax2,0)<=0 AND ISNULL(LineTax3,0)<=0) THEN 'Y' ELSE 'N' END AS Exempted,COM_TaxGroups.TaxGroupCode
FROM PUR_PurchaseInvoices LEFT OUTER JOIN PUR_PurchaseHeaders ON PUR_PurchaseInvoices.PurchaseHeaderID=PUR_PurchaseHeaders.PurchaseHeaderID
LEFT OUTER JOIN PUR_PurchaseHeaderLines ON PUR_PurchaseHeaderLines.PurchaseHeaderID=PUR_PurchaseHeaders.PurchaseHeaderID
LEFT OUTER JOIN COM_TaxGroups ON PUR_PurchaseHeaderlines.LineTaxGroupID=COM_TaxGroups.TaxGroupID
LEFT OUTER JOIN INV_Products ON PUR_PurchaseHeaderLines.ProductCode=INV_Products.ProductCode
LEFT OUTER JOIN INV_MeasurementUnits ON INV_MeasurementUnits.UnitCode=INV_Products.UnitCode
LEFT OUTER JOIN COM_StateMaster ON COM_StateMaster.StateID=PUR_PurchaseHeaders.HeaderStateIDOfSupply
LEFT OUTER JOIN VU_PUR_Suppliers ON PUR_PurchaseHeaders.SupplierID=VU_PUR_Suppliers.SupplierID
WHERE PUR_PurchaseHeaders.TaxID1 IS NULL AND PUR_PurchaseHeaders.TaxID2 IS NULL AND PUR_PurchaseHeaders.TaxID3 IS NULL AND ExtendedPrice>0
UNION
SELECT COM_StateMaster.State,COM_StateMaster.StateCode,HeaderStateIDOfSupply AS StateID,OutSideState,'Inward' AS TaxationType,'Purchase' AS TransactionType,COALESCE(PUR_PurchaseHeaders.ReferenceNo, CONVERT(nvarchar,PurchaseInvoiceNo)) AS VoucherNo,InvoiceDate AS VoucherDate,
VU_PUR_Suppliers.Supplier AS PartyName,VU_PUR_Suppliers.GSTNo AS GSTNo,VU_PUR_Suppliers.StateID AS PartyStateID,VU_PUR_Suppliers.State AS PartyState,VU_PUR_Suppliers.BusinessType,VU_PUR_Suppliers.StateCode AS PartyStateCode,
PUR_PurchaseHeaderLines.ProductCode,INV_Products.HSNCode,ISNULL(INV_MeasurementUnits.UQC,INV_Products.UnitCode) AS UnitCode,PUR_PurchaseHeaderLines.Quantity,
ExtendedPrice + ISNULL((LinePrice*TaxRate1/100),0)+ISNULL((LinePrice*TaxRate2/100),0)+ISNULL((LinePrice*TaxRate3/100),0) AS InvoiceValue,LinePrice AS TaxableAmount,PUR_PurchaseHeaders.SupplierReferenceNo AS SupplierReferenceNo,
CASE WHEN Tax1GLCode=2490 THEN TaxRate1 WHEN Tax2GLCode=2490 THEN TaxRate2 WHEN Tax3GLCode=2490 THEN TaxRate3 END AS CGSTPercentage,CASE WHEN (ISNULL(Tax1,0)<=0 AND ISNULL(Tax2,0)<=0 AND ISNULL(Tax3,0)<=0) THEN 'Y' ELSE 'N' END AS Exempted,COM_TaxGroups.TaxGroupCode
FROM PUR_PurchaseInvoices LEFT OUTER JOIN PUR_PurchaseHeaders ON PUR_PurchaseInvoices.PurchaseHeaderID=PUR_PurchaseHeaders.PurchaseHeaderID
LEFT OUTER JOIN PUR_PurchaseHeaderLines ON PUR_PurchaseHeaderLines.PurchaseHeaderID=PUR_PurchaseHeaders.PurchaseHeaderID
LEFT OUTER JOIN COM_TaxGroups ON PUR_PurchaseHeaders.HeaderTaxGroupID=COM_TaxGroups.TaxGroupID
LEFT OUTER JOIN INV_Products ON PUR_PurchaseHeaderLines.ProductCode=INV_Products.ProductCode
LEFT OUTER JOIN INV_MeasurementUnits ON INV_MeasurementUnits.UnitCode=INV_Products.UnitCode
LEFT OUTER JOIN COM_StateMaster ON COM_StateMaster.StateID=PUR_PurchaseHeaders.HeaderStateIDOfSupply
LEFT OUTER JOIN VU_PUR_Suppliers ON PUR_PurchaseHeaders.SupplierID=VU_PUR_Suppliers.SupplierID
WHERE (PUR_PurchaseHeaders.TaxID1 IS NOT NULL OR PUR_PurchaseHeaders.TaxID2 IS NOT NULL OR PUR_PurchaseHeaders.TaxID3 IS NOT NULL) AND ExtendedPrice>0
Tags: SQL

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
  4. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100