Solved

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

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 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