Solved

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

Posted on 2010-11-23
5
637 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
  • 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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

685 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