Solved

The multi-part identifier could not be bound.

Posted on 2014-03-21
6
9,272 Views
Last Modified: 2014-03-21
Ok EE...this could be another /facepalm moment for me, but I've been wracking my brain so now it's your turn.

I have two tables, as follows

DocumentHeaders
DocumentItems

Essentially, headers is the detail like customerID, sales rep, order date and total, and Items is the line detail.

I'm trying to run a query for management that will tell them :
1) Date of Order, 2) Order Total, 3) Purchase Vehicle, 4) number of line items

I have the following query that in theory would give me just that:

select H.DocDate, H.GrandTotal, H.CustomText03, count(I.DocID) as Lines
From DocumentHeaders as H, DocumentItems as I 
JOIN DocumentItems on DocumentHeaders.ID = DocumentItems.DocID
where H.CustomText05 = 'UDA00120493523' and
DocType = 'Order' and
DocStatus = 'Open' or DocStatus = 'Exported'

Open in new window


Unfortunately I get the following error:  "The multi-part identifier "DocumentHeaders.ID" could not be bound."

I've spent a few hours re-working this seemingly simple query and scouring the webs...I leave it to you to show me the error of my ways.
0
Comment
Question by:cjake2299
  • 2
  • 2
  • 2
6 Comments
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 39946608
Try

select H.DocDate, H.GrandTotal, H.CustomText03, count(I.DocID) as Lines
From DocumentHeaders as H, DocumentItems as I 
JOIN DocumentItems on DocumentHeaders.ID = DocID
where H.CustomText05 = 'UDA00120493523' and
DocType = 'Order' and
DocStatus = 'Open' or DocStatus = 'Exported'

Open in new window

0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 39946611
Or try

select H.DocDate, H.GrandTotal, H.CustomText03, count(I.DocID) as Lines
From DocumentHeaders as H, DocumentItems as I 
JOIN I on H.ID = I.DocID
where H.CustomText05 = 'UDA00120493523' and
DocType = 'Order' and
DocStatus = 'Open' or DocStatus = 'Exported'

Open in new window


An thorough explanation for this error and how it can be resolved is found at

http://www.sql-server-helper.com/error-messages/msg-4104.aspx
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39946674
Try it this way:
SELECT  H.DocDate,
        H.GrandTotal,
        H.CustomText03,
        COUNT(*) AS Lines
FROM    DocumentHeaders AS H
        INNER JOIN DocumentItems AS I ON H.ID = I.DocID
WHERE   H.CustomText05 = 'UDA00120493523'
        AND DocType = 'Order'				-- Missing alias
        AND DocStatus = 'Open'				-- Missing alias
        OR DocStatus = 'Exported'			-- Missing alias
GROUP BY H.DocDate,
        H.GrandTotal,
        H.CustomText03

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cjake2299
ID: 39946737
Anthony, yours worked!  I had to delete the "OR" from the where statement to get the correct number, otherwise it jacked and sent me everything that I requested or had a status of exported.... easier to run two queries anyways.

@WalkaboutTrigger,

thanks for the help, but unfortunately both of your solutions resulted in errors:

Error Message from first post:

"Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "DocumentHeaders.ID" could not be bound.
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'DocID'."


Error Message from second post:

"Msg 208, Level 16, State 1, Line 1
Invalid object name 'I'."
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39946748
 I had to delete the "OR" from the where statement to get the correct number, otherwise it jacked and sent me everything that I requested or had a status of exported.... easier to run two queries anyways.
Assuming the logic is correct try it this way:
WHERE   H.CustomText05 = 'UDA00120493523'
        AND DocType = 'Order'		       -- Missing alias
        AND DocStatus IN ('Open', 'Exported')	-- Missing alias

Open in new window

0
 

Author Comment

by:cjake2299
ID: 39946793
That'll do it! Thanks again!
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question