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
Solved

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

Posted on 2010-11-23
5
635 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:ewangoya
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query with cast 38 53
SQL Server Count where two id types exist in column 8 30
SQL Server 2012 r2 - Sum totals 2 28
Sql query with where clause 2 34
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
     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 …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

829 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