the Multi-Part iedentifier "" could not be bound

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

the Multi-Part iedentifier "" could not be bound

Author

Topic

NelsonChew
Starting Member

3 Posts

Posted - 2009-08-03 : 14:16:48

hello
I have created a SQL query which I am using in an Excel data connection.
When i hard enter Criteria it works fine but when I create a parameter query I get the Multi-Part iedentifier "" could not be bound errors.

here is my query:

---------------------------------------------------------------------
SELECT

cij.SalesId
, cij.CustGroup
, cij.OrderAccount
, cij.InvoiceId
, cij.PurchaseOrder
, cij.InvoiceAccount
, cij.InvoiceDate

, cij.InvoicingName
, cij.InvoicingAddress
, cij.InvCountryRegionId

, cij.DeliveryStreet as 'Delivery Address'
, cij.DeliveryCity as 'Delivery City'
, cij.DlvCountryRegionId as 'Delivery Country'
, cij.CurrencyCode
, CustTable.VATNum as 'Customer VAT#'
, 'FR44 511 887 085' as ' VAT Code'
, CustTable.dlvterm as 'Incoterms'
, sum(CustInvoiceTrans.LineAmount) as 'VAT Excluded'
,
( SELECT count(so.SalesId)
FROM SoBox so
WHERE so.DATAAREAID = cij.dataareaid AND
so.salesid = cij.salesid
group by so.dataareaid)
as '# of Parcels'

, sum(InventTable.TaraWeight) as 'Gross Weight'
, sum(InventTable.NetWeight) as 'Net Weight'
, 0 as 'VAT Amount'
, 'VAT Code' =
CASE cij.DlvCountryRegionId
WHEN 'CH' THEN 'Exempt'
WHEN 'RU' THEN 'Exempt'
ELSE 'Zero'
END
, 'Contract Terms' =
CASE cij.DlvCountryRegionId
WHEN 'FR' THEN 'France'
WHEN 'CH' THEN 'Non EU'
WHEN 'RU' THEN 'Non EU'
ELSE 'EU Not France'
END
FROM CustInvoiceJour cij
inner join CustTable
on cij.InvoiceAccount = CustTable.AccountNum
and cij.DataAreaId = CustTable.DataAreaId

inner join CustInvoiceTrans
on cij.SalesId = CustInvoiceTrans.SalesId
and cij.DataAreaId = CustInvoiceTrans.DataAreaId

inner join InventTable
on CustInvoiceTrans.ItemId = InventTable.Itemid
and CustInvoiceTrans.DataAreaId = InventTable.DataAreaId

where cij.InvoiceDate >= ?
and cij.InvoiceDate <= ?
and cij.dataareaid = ?

and cij.RefNum = 0
and cij.invoiceaccount not in ('amaz0000', 'amaz0002')
and cij.sumtax = 0

group by
cij.dataareaid
, cij.SalesId
, cij.CustGroup
, cij.OrderAccount
, cij.InvoiceId
, cij.PurchaseOrder
, cij.InvoiceAccount
, cij.InvoiceDate
, CustTable.dlvterm
, cij.DeliveryStreet
, cij.DeliveryCity

, cij.InvoicingName
, cij.InvoicingAddress
, cij.InvCountryRegionId

, cij.DlvCountryRegionId
, cij.CurrencyCode
, CustTable.VATNum
order by cij.salesid

---------------------------------------------------------------------

where cij.InvoiceDate >= ?
and cij.InvoiceDate <= ?
and cij.dataareaid = ?
When the above has specific values the query work fine. When the ? is used for the parameter query I get the following 3 errors:

the Multi-Part iedentifier "CustInvoiceTrans.DataAreaId" could not be bound
the Multi-Part iedentifier "cij.InvoiceDate" could not be bound
the Multi-Part iedentifier "cij.dataareaid" could not be bound

Can you tell me what is causing this?
Thanks!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-03 : 14:25:59

Do you pass parameters having space in their value?

N 56°04'39.26"
E 12°55'05.63"

NelsonChew
Starting Member

3 Posts

Posted - 2009-08-03 : 14:31:36

the parameter do not have a space in their value.
they are linked to specific excel cells and set to update when the values are change

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-03 : 15:02:29

How about now using string literals for column names?

SELECT		cij.SalesId
		, cij.CustGroup
		, cij.OrderAccount
		, cij.InvoiceId
		, cij.PurchaseOrder
		, cij.InvoiceAccount
		, cij.InvoiceDate
		, cij.InvoicingName 
		, cij.InvoicingAddress 
		, cij.InvCountryRegionId 
		, cij.DeliveryStreet as [Delivery Address]
		, cij.DeliveryCity as [Delivery City]
		, cij.DlvCountryRegionId as [Delivery Country]
		, cij.CurrencyCode
		, CustTable.VATNum as [Customer VAT#]
		, 'FR44 511 887 085' as [VAT Code]
		, CustTable.dlvterm as Incoterms
		, sum(CustInvoiceTrans.LineAmount) as [VAT Excluded]
		, ( SELECT count(so.SalesId)
			FROM SoBox so
			WHERE so.DATAAREAID = cij.dataareaid AND 
			so.salesid = cij.salesid
			group by so.dataareaid) as [# of Parcels]
		, sum(InventTable.TaraWeight) as [Gross Weight]
		, sum(InventTable.NetWeight) as [Net Weight]
		, 0 as [VAT Amount]
		, [VAT Code] = CASE cij.DlvCountryRegionId 
				WHEN 'CH' THEN 'Exempt'
				WHEN 'RU' THEN 'Exempt'
				ELSE 'Zero' 
			END
		, [Contract Terms] = CASE cij.DlvCountryRegionId 
					WHEN 'FR' THEN 'France'
					WHEN 'CH' THEN 'Non EU'
					WHEN 'RU' THEN 'Non EU'
					ELSE 'EU Not France' 
				END 
FROM		CustInvoiceJour cij
inner join	CustTable on cij.InvoiceAccount = CustTable.AccountNum
			and cij.DataAreaId = CustTable.DataAreaId
inner join	CustInvoiceTrans on cij.SalesId = CustInvoiceTrans.SalesId
			and cij.DataAreaId = CustInvoiceTrans.DataAreaId
inner join	InventTable on CustInvoiceTrans.ItemId = InventTable.Itemid
			and CustInvoiceTrans.DataAreaId = InventTable.DataAreaId
where		cij.InvoiceDate >= ?
		and cij.InvoiceDate <= ?
		and cij.dataareaid = ?
		and cij.RefNum = 0 
		and cij.invoiceaccount not in ('amaz0000', 'amaz0002')
		and cij.sumtax = 0
group by	cij.dataareaid
		, cij.SalesId
		, cij.CustGroup
		, cij.OrderAccount
		, cij.InvoiceId
		, cij.PurchaseOrder
		, cij.InvoiceAccount
		, cij.InvoiceDate
		, CustTable.dlvterm 
		, cij.DeliveryStreet 
		, cij.DeliveryCity
		, cij.InvoicingName 
		, cij.InvoicingAddress 
		, cij.InvCountryRegionId 
		, cij.DlvCountryRegionId
		, cij.CurrencyCode
		, CustTable.VATNum
order by	cij.salesid

N 56°04'39.26"
E 12°55'05.63"

NelsonChew
Starting Member

3 Posts

Posted - 2009-08-03 : 18:04:08

That does not seem to make any difference...still the same error...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources