Solved

Getting error The multi-part identifier "TransactionEntry.Quantity" could not be bound.

Posted on 2010-11-23
5
645 Views
Last Modified: 2012-05-10
declare @TransID as int;
select top 1 @TransID="Transaction".ID
FROM (TransactionEntry RIGHT JOIN "Transaction" ON TransactionEntry.TransactionID = "Transaction".ID)
GROUP BY "Transaction".ID, TransactionEntry.Quantity, "Transaction".Time
HAVING (((TransactionEntry.Quantity)>=1))
order by "Transaction".Time DESC;
UPDATE Item
SET SubDescription1 = TransactionEntry.Quantity
WHERE Item.ID IN (
SELECT top 500 Item.ID
FROM (TransactionEntry RIGHT JOIN "Transaction" ON TransactionEntry.TransactionID = "Transaction".ID) LEFT JOIN Item ON TransactionEntry.ItemID = Item.ID
GROUP BY "Transaction".ID, Item.ID, Item.ItemLookupCode,Item.Description, TransactionEntry.Quantity, "Transaction".Time
HAVING (((TransactionEntry.Quantity)>=1) and ("Transaction".ID = @TransID ))
ORDER BY "Transaction".Time DESC)


I'm trying to set Item.subdescription1 to TransactionEntry.Quantity, but it's giving me an error.

How do re-write the SQL  statement to fix this error and achieve the result that I want.
0
Comment
Question by:solarisinfosys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34202708

declare @TransID as int;
select top 1 @TransID="Transaction".ID
FROM TransactionEntry
RIGHT JOIN "Transaction" ON TransactionEntry.TransactionID = "Transaction".ID
GROUP BY "Transaction".ID, TransactionEntry.Quantity, "Transaction".Time
HAVING ((TransactionEntry.Quantity)>=1)
order by "Transaction".Time DESC;

UPDATE Item
SET SubDescription1 = TransactionEntry.Quantity
WHERE Item.ID IN (
SELECT top 500 Item.ID
FROM TransactionEntry
RIGHT JOIN "Transaction" ON TransactionEntry.TransactionID = "Transaction".ID
LEFT JOIN Item ON TransactionEntry.ItemID = Item.ID
GROUP BY "Transaction".ID, Item.ID, Item.ItemLookupCode,Item.Description, TransactionEntry.Quantity, "Transaction".Time
HAVING (((TransactionEntry.Quantity)>=1) and ("Transaction".ID = @TransID ))
ORDER BY "Transaction".Time DESC)

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34202737
your SQL is more than doubtful....

and indeed, your UPDATE does not have the correct implemention, in terms that the table Transaction is NOT on the same level as the UPDATE, only in the subquery.
but also the first query seems not correct, because the group by, with HAVING >= 1 , and the RIGHT join all don't make too much sense to me.

could you clarify, please, what your script is trying to achieve?
0
 

Author Comment

by:solarisinfosys
ID: 34203226
angelIII:
 
My script is trying to increment the MTD (Month to Date Qty) field which is called subDescription1 every time there is a sale transaction. The idea is to take the quantity from the TransactionEntry table and add it to the number in the subDescription1 field in the item table. But only perform this for the items in the most recent transaction. SQL Screen shot
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34203256
ok, let's see:

declare @TransID as int;
select top 1 @TransID= t.ID
FROM [Transaction] t
LEFT JOIN TransactionEntry te
  ON t2.TransactionID = t.ID 
WHERE t2.Quantity >=1
order by t.[Time] DESC;

UPDATE i
  SET SubDescription1 = te.Quantity
  FROM Item i
  JOIN TransactionEntry te
    ON t2.ItemID = i.ID
  AND t2.TransactionID = @transid

Open in new window


I had written an article on the UPDATE with JOIN, if you want to read:
http://www.experts-exchange.com/A_1517.html
0
 

Author Closing Comment

by:solarisinfosys
ID: 34207133
angelIII

  Your solution is awesome; Very elegant and clean. Thanks for the code. I also read your article on the UPDATE with JOIN. It was very helpful and informative. It helped me understand your solution.

BTW, I think you meant to type te instead of t2, b/c when I tweaked your code from t2 to te, it worked perfectly.

Thanks again
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

690 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