Solved

The multi-part identifier could not be bound.

Posted on 2014-03-21
6
8,746 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now