Click here to Skip to main content
14,388,768 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Posted
Updated 4-Nov-19 7:22am
v3
Comments
F-ES Sitecore 4-Nov-19 10:08am
   
You can't possibly expect someone to pick through that complex statement with no knwoedge of the data. Regardless, the error is in reference to "UR_PurchaseHeaders" but there is no such table in your query, it is "PUR_PurchaseHeaders". Maybe whatever is using "UR_PurchaseHeaders" is a typo and needs the "P" added to the front.
harish kashyap01 4-Nov-19 10:18am
   
Thank You for the reply.
Ive changed it to 'PUR'
Rate this:
Please Sign up or sign in to vote.

Solution 2

A couple of suggestions that may help you out in the future:

1. Use a dedicated SQL Editor such as SSMS (SQL Server Management Studio) and utilize the query builder functionality. The intellisense offered should generate some of the red underlines where is code is going astray.
There are also other views for SQL, such as design view which will show a graphical overlay.
Azure Data Studio can also be helpful with the intellisense,

2. Use some formatting standards; it makes is much easier to read and troubleshoot. Especially when you reach out for help.

3. Use aliases for your table names. Coupled with formatting, it can greatly help in the readability department.

4. When you copy and paste code, please paste it as a code block. Sometimes you can even see the error in the preview it generates. Regardless, it makes it easier for others to help you when they attempt to copy your code into a desktop application.

I do not know if your problem is currently resolved or not; but by attempting to format the code you posted it looks like you may have missed a comma to separate two of the values being returned. I placed a comment in the code below for you to investigate
SELECT	cs.State
,	cs.StateCode
,	HeaderStateIDOfSupply AS StateID
,	OutSideState
,	'Inward' AS TaxationType
,	'Purchase' AS TransactionType
,	COALESCE(ph.ReferenceNo, CONVERT(nvarchar, PurchaseInvoiceNo)) AS VoucherNo 
,	InvoiceDate AS VoucherDate
,	ps.Supplier AS PartyName
,	ps.GSTNo AS GSTNo
,	ps.StateID AS PartyStateID
,	ps.State AS PartyState
,	ps.BusinessType
,	ps.StateCode AS PartyStateCode
,	pl.ProductCode
,	ip.HSNCode
,	ISNULL(im.UQC, ip.UnitCode) AS UnitCode
,	pl.Quantity
,	ExtendedPrice AS InvoiceValue
,	LinePrice AS TaxableAmount

--	Check out this next line
,	ph.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
,	ct.TaxGroupCode

FROM PUR_PurchaseInvoices		pi
LEFT OUTER JOIN PUR_PurchaseHeaders	ph ON pi.PurchaseHeaderID	= ph.PurchaseHeaderID
LEFT OUTER JOIN PUR_PurchaseHeaderLines	pl ON pl.PurchaseHeaderID	= ph.PurchaseHeaderID
LEFT OUTER JOIN COM_TaxGroups		ct ON pl.LineTaxGroupID		= ct.TaxGroupID
LEFT OUTER JOIN INV_Products		ip ON pl.ProductCode		= ip.ProductCode
LEFT OUTER JOIN INV_MeasurementUnits	im ON im.UnitCode		= ip.UnitCode
LEFT OUTER JOIN COM_StateMaster		cs ON cs.StateID		= ph.HeaderStateIDOfSupply
LEFT OUTER JOIN VU_PUR_Suppliers	ps ON ph.SupplierID		= ps.SupplierID

WHERE	(ph.TaxID1 IS NULL)
AND	(ph.TaxID2 IS NULL)
AND	(ph.TaxID3 IS NULL)
AND	(ExtendedPrice > 0)

UNION
SELECT	cs.State
,	cs.StateCode
,	HeaderStateIDOfSupply AS StateID
,	OutSideState
,	'Inward' AS TaxationType
,	'Purchase' AS TransactionType
,	COALESCE(ph.ReferenceNo, CONVERT(nvarchar, PurchaseInvoiceNo)) AS VoucherNo
,	InvoiceDate AS VoucherDate
,	ps.Supplier AS PartyName
,	ps.GSTNo AS GSTNo
,	ps.StateID AS PartyStateID
,	ps.State AS PartyState
,	ps.BusinessType
,	ps.StateCode AS PartyStateCode
,	pl.ProductCode
,	ip.HSNCode
,	ISNULL(im.UQC
,	ip.UnitCode) AS UnitCode
,	pl.Quantity
,	ExtendedPrice
		+ ISNULL((LinePrice*TaxRate1/100), 0)
		+ ISNULL((LinePrice*TaxRate2/100), 0)
		+ ISNULL((LinePrice*TaxRate3/100), 0)
	AS InvoiceValue
,	LinePrice AS TaxableAmount
,	ph.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
,	ct.TaxGroupCode

FROM PUR_PurchaseInvoices		pi
LEFT OUTER JOIN PUR_PurchaseHeaders	ph ON pi.PurchaseHeaderID	= ph.PurchaseHeaderID
LEFT OUTER JOIN PUR_PurchaseHeaderLines	pl ON pl.PurchaseHeaderID	= ph.PurchaseHeaderID
LEFT OUTER JOIN COM_TaxGroups		ct ON ph.HeaderTaxGroupID	= ct.TaxGroupID
LEFT OUTER JOIN INV_Products		ip ON pl.ProductCode		= ip.ProductCode
LEFT OUTER JOIN INV_MeasurementUnits	im ON im.UnitCode		= ip.UnitCode
LEFT OUTER JOIN COM_StateMaster		cs ON cs.StateID		= ph.HeaderStateIDOfSupply
LEFT OUTER JOIN VU_PUR_Suppliers	ps ON ph.SupplierID		= ps.SupplierID

WHERE	(	ph.TaxID1 IS NOT NULL
	OR	ph.TaxID2 IS NOT NULL
	OR	ph.TaxID3 IS NOT NULL
)
AND	(ExtendedPrice > 0)
   
Comments
Richard Deeming 5-Nov-19 15:25pm
   
Speaking of formatting standards, I've never understood why people put the comma at the start of the line.

Sure, it makes it easier to add / remove / comment the last column in the list. But at the same time, it makes it harder to do that for the first column in the list. You've just moved the problem from one end to the other.

Consider my OCD "triggered". :)
MadMyche 5-Nov-19 16:15pm
   
When checking someone else's code it is a sanity check, to make sure that the commas are all there. Normally I only have the main parts (SELECT/FROM/WHERE) at the left margin
Rate this:
Please Sign up or sign in to vote.

Solution 1

Read the error message:
The multi-part identifier "UR_PurchaseHeaders.SupplierReferenceNo" could not be bound.
It mjeasn what it says: there is no column called "SupplierReferenceNo" in the table "UR_PurchaseHeaders". Almost certainly, it was "PUR_PurchaseHeaders"

So check your DB, and find out where the column actually is, and amend your query to match.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




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