Invalid Column Name in Update Statement

Transact-SQL
1

I'm getting an error on the line that has the "case" statement. The error is:

Invalid column name 'ProductTransactionStatusCode'.

I have confirmed that the column name, which is of VARCHAR type, is in fact in the table ProductInformation_W

The SQL 2008 statement is:

UPDATE
ProductInformation
SET
ProductInformation.CollectionDate = h.CollectionDate
,ProductInformation.ExpirationDate = CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, h.ExpirationDate, 120), 10) + ' ' + h.ExpirationTime + ':00.000', 121)
,ProductInformation.CreationDate = h.CreationDate
,ProductInformation.CollectionLocationCode = h.CollectionLocationCode
,ProductInformation.ProductTypeCode = h.ProductTypeCode
,ProductInformation.ProductStatusCode = h.ProductStatusCode
,ProductInformation.InventoryClassCode = h.InventoryClassCode
,ProductInformation.StockStateCode = h.StockStateCode
,ProductInformation.Imported = h.Imported
,ProductInformation.Exported = (Case When h.ProductTransactionStatusCode In ('048', '051') Then h.Exported Else p.ProductTransactionStatusCode End)
,ProductInformation.ProcessingLocationCode = h.ProcessingLocationCode
,ProductInformation.RunDateTime = getdate()
FROM
ProductInformation p
INNER JOIN
ProductInformation_W h
ON
p.UnitNumber = h.UnitNumber AND p.ProductCode = h.ProductCode AND p.Suffix = h.Suffix

2

You have h.ProductTransactionStatusCode and p.ProductTransactionStatusCode in the query in the case expression. You verified that it is in the ProductInformation_W table, which is aliased to h. But is there a column by the same name in ProductInformation table, which is aliased to p as well?

3

Good call! No there isn't, in fact - thanks!

4

And the field actually should be p.Exported.