"The multi-part identifier error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

"The multi-part identifier error

Author

Topic

bdkonin
Starting Member

3 Posts

Posted - 2008-12-01 : 15:17:19

Hi,

I am receiving the error message The multi-part identifier "#Return.Qty" could not be bound. Can someone look at my SQl belowand tell me what is it that would cause this error? I have built a temp table and I am looking to insert a second value.

--Temp Table/Main Table
select sdlitm as 'Item', sddsc1 as 'Description', sum(sdsoqs) as 'Qty Shipped',
0 as 'QtyReturnedDamaged'
into #Orders
from proddta.f4211
where sdaddj > '108001'
and sddcto = 'SO'
and sdlitm not like 'FRT%'
group by sdlitm, sddsc1
order by sdlitm asc

--Returned
select sdlitm as 'Item', sddsc1 as 'Description', sum(sdsoqs) as 'Qty'
into #Return
from proddta.f4211
where sdaddj > '108001'
and sddcto = 'SH'
and sdlitm not like 'FRT%'
group by sdlitm, sddsc1
order by sdlitm asc

--Update
Update #Orders
Set QtyReturnedDamaged = #Return.Qty
where #Orders.Item = #Return.Item
select *
from #Orders

Drop table #Orders
Drop table #Return

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-01 : 15:25:58

In your Update the table #Return is missing.
Try this:


update o
set QtyReturnedDamaged = r.Qty
from #Orders o
inner join #Return r on r.Item = o.Item

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

bdkonin
Starting Member

3 Posts

Posted - 2008-12-01 : 15:54:49

Webfred,

Thank you very much for the quick reply. I tried what you said and it appeared to work. I must admit I am a little confused as to why? Looking at the Update portion of my query, I do have #Retyrn there? The differences I see in what you did are:

1) Instead of "Update #Orders" you used "Update o". Only difference is with the alias.

2) Instead of "Set QtyReturnedDamaged = #Return.Qty" you used "set QtyReturnedDamaged = r.Qty". Only difference is with the alias.

3)You inserted "From #Orders o". What did this do?

4) Instead of "where #Orders.Item = #Return.Item" you used "inner join #Return r on r.Item = o.Item". How does this differ other than the alias use?

Again though, just to stress that I really appreciate your help. In the past what i wrote had worked many times so that is why I am confused as to the difference. Thanks again.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-01 : 16:08:41

Your update-request needs to join the 2 involved tables.
1) I use the alias because i need to write the real tablename in the from-clause.
2) It is not the only difference because in your code there is no from or join to point the table #Return.
3) See 1) I need it in from-clause so I can join the other table
4) This differs because in your code isn't any join

Hope that helps a little though my bad english

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

bdkonin
Starting Member

3 Posts

Posted - 2008-12-01 : 17:02:29

Thank you very much for your help. It is much appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-01 : 17:07:12

Welcome