Unknown Column ... in Where Clause in SQL statement with UNION

[John Noble]

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