Solved

The multi-part identifier could not be bound.

Posted on 2014-03-21
6
8,903 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
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.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
best counters for cpu high usage 3 31
TSQL - How to declare table name 26 30
Query Syntax 17 34
SQL Syntax: How to force case sensitive query? 2 29
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

773 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