The multi-part identifier could not be bound.

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.
cjake2299Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
Darrell PorterEnterprise Business Process ArchitectCommented:
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
 
Darrell PorterEnterprise Business Process ArchitectCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
cjake2299Author Commented:
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
 
Anthony PerkinsCommented:
 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
 
cjake2299Author Commented:
That'll do it! Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.