MySQL Forums
Forum List  »  Stored Procedures

Unknown Column ... in Where Clause in SQL statement with UNION
Posted by: John Noble
Date: September 14, 2020 08:05AM

Hi folks,

Im struggling here with a stored procedure. I am building up an SQL statement that queries the same fields from 2 different table. ]SalesHistoryArchive] and [SalesHistory].

But I am getting Unknown column 'salesHistoryArchive.productID' in 'where clause' But the column is there.

Here is the finished SQL...

Can anyone see what is wrong

SELECT salesHistoryArchive.productID,
products.description as productDescription,
salesHistoryArchive.lineWeight,
salesHistoryArchive.lineValue
FROM salesHistoryArchive
INNER JOIN products ON products.productID = salesHistoryArchive.productID
WHERE despatchDate BETWEEN '2020-01-01' and '2020-06-30' AND
SUBSTRING(salesHistoryArchive.productID FROM 1 FOR 5) = @vSpeciesID AND
salesHistoryArchive.countryID = @vCountryID AND
salesHistoryArchive.customerID = @vCustomerID AND
salesHistoryArchive.transactionType = @vTransactionType
GROUP BY productDescription
UNION ALL
SELECT salesHistory.productID,
products.description as productDescription,
salesHistory.lineWeight,
salesHistory.lineValue
FROM salesHistory
INNER JOIN products ON products.productID = salesHistory.productID
WHERE despatchDate BETWEEN '2020-01-01' and '2020-06-30' AND
SUBSTRING(salesHistoryArchive.productID FROM 1 FOR 5) = @vSpeciesID AND
salesHistoryArchive.countryID = @vCountryID AND
salesHistoryArchive.customerID = @vCustomerID AND
salesHistoryArchive.transactionType = @vTransactionType AND
salesHistory.transactionType = @vTransactionType
GROUP BY productDescription

Options: ReplyQuote


Subject
Views
Written By
Posted
Unknown Column ... in Where Clause in SQL statement with UNION
312
September 14, 2020 08:05AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.