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

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.
solarisinfosysAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Ephraim WangoyaCommented:

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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
solarisinfosysAuthor Commented:
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
 
solarisinfosysAuthor Commented:
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
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.