?
Solved

The multi-part identifier could not be bound.

Posted on 2014-03-21
6
Medium Priority
?
12,422 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 2000 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

864 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