Sql Query Whats wrong?

Question

Post reply

Sql Query Whats wrong?

npeters 86796

Old Hand

Points: 310
More actions
December 21, 2011 at 8:34 am

#391178

When i run this Query i get the error below i cannot see what wrong.
may someone help?
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.CustomerID" could not be bound.
SELECT a.TransactionNumber ,
a.StoreID ,
a.Transaction_Date ,
a.CustomerID ,
a.StoreName ,
a.LogicalStoreName ,
a.ItemLookupCode ,
a.Description ,
a.ClassID ,
a.Quantity ,
a.FullPrice ,
a.Full_Cost ,
a.SalesRepID ,
a.Name ,
a.Ship ,
b.EmailAddress ,
b.PhoneNumber ,
b.FirstName ,
b.LastName ,
b.HQID
FROM dbo.SalesReportRMS AS a ,
[10.1.1.18\RMS20].ICLHQ.dbo.Customer AS b
JOIN [10.1.1.18\RMS20].ICLHQ.dbo.Customer ON b.HQID = a.CustomerID
WHERE a.Transaction_Date BETWEEN 'dec 1 2011 17:00:00'
AND 'dec 1 2011 23:59:00'
AND a.StoreID IN ( 40, 5, 10, 13, 14, 19, 23, 24, 30, 31, 28 )

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

Slightly unusual syntax. Try this

SELECT a.TransactionNumber ,

a.StoreID ,

a.Transaction_Date ,

a.CustomerID ,

a.StoreName ,

a.LogicalStoreName ,

a.ItemLookupCode ,

a.Description ,

a.ClassID ,

a.Quantity ,

a.FullPrice ,

a.Full_Cost ,

a.SalesRepID ,

a.Name ,

a.Ship ,

b.EmailAddress ,

b.PhoneNumber ,

b.FirstName ,

b.LastName ,

b.HQID

FROM dbo.SalesReportRMS a JOIN

[10.1.1.18\RMS20].ICLHQ.dbo.Customer b

ON b.HQID = a.CustomerID

WHERE a.Transaction_Date BETWEEN 'dec 1 2011 17:00:00'

AND 'dec 1 2011 23:59:00'

AND a.StoreID IN ( 40, 5, 10, 13, 14, 19, 23, 24, 30, 31, 28 )

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

npeters 86796 Old Hand Points: 310 More actions · Answer 2

npeters 86796

Old Hand

Points: 310

December 21, 2011 at 8:40 am

#1424290

Thank you, i think i am losing my skills!! 🙂

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Does this help?

SELECT

--FROM SalesReportRMS

a.TransactionNumber, a.StoreID, a.Transaction_Date, a.CustomerID, a.StoreName,

a.LogicalStoreName, a.ItemLookupCode, a.Description, a.ClassID, a.Quantity, a.FullPrice,

a.Full_Cost, a.SalesRepID, a.NAME, a.Ship,

--FROM [10.1.1.18\RMS20].ICLHQ.dbo.Customer

b.EmailAddress, b.PhoneNumber, b.FirstName, b.LastName, b.HQID

FROM dbo.SalesReportRMS AS a

JOIN [10.1.1.18\RMS20].ICLHQ.dbo.Customer b ON b.HQID /*[10.1.1.18\RMS20].ICLHQ.dbo.Customer*/ = a.CustomerID /*SalesReportRMS*/

WHERE a.Transaction_Date BETWEEN 'dec 1 2011 17:00:00' AND 'dec 1 2011 23:59:00' AND a.StoreID IN (40, 5, 10, 13, 14, 19, 23, 24, 30, 31, 28)

--EDIT--

Arrgh, way too slow 😀

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/