How do I use the calculated field (SumQuantity) below in my where clause? I'm getting error: invalid column name SumQuantity. Thank you in advance, BenSELECT [POItem].[POID] ,[POItem].[ItemID] ,[POItem].[Description] ,[POItem].[Status] ,[POItem].[ProjID] ,[POItem].[SubJobID],[POItem].[ItemType] ,[POItem].[PartID] ,[POItem].[Quantity] ,[POItem].[UOM] ,[POItem].[Price] ,[POItem].[POClassID],[POItem].[TxnLineID] ,[POItem].[DivisionID] ,[POItem].[DateTimeLastChanged] ,(SELECT SUM(dbo.[POItemTrans].[QuantityReceived])FROM [Athena].[TPMS].[dbo].[POItemTrans] WHERE [POItemTrans].[POID] = [POItem].[POID] AND [POItemTrans].[ItemID] = [POItem].[ItemID])AS SumQuantity FROM [dbo].[POItem] , [dbo].[PO] [PO1] WHERESumQuantity != [POItem].[Quantity]ORDER BY [POItem].[Status], [POItem].[POID], [POItem].[ItemID]
You cannot use alias like that in the WHERE condition.. you either need to replace the alias with the entire expression or redo the query in a different way..perhaps get the results of the query into a temp table/table variable and then do a SELECT From it..
It works if you use the whole query, minus the WHERE clause, as a derived table, then filter on the derived table. SELECT x.* FROM (SELECT POItem.POID, (SELECT SUM(...) FROM ... WHERE ...) AS SumQuantity) FROM mytable) AS x WHERE x.SumQuantity <> x.Quantity Finding aggregate values where the sum value is different from the value on the row - that should be the same as finding rows with the same (set of) key value(s) but different values - SELECT t1.* FROM MyTable t1 WHERE EXISTS (SELECT t2.* FROM MyTable t2 WHERE t2.key = t1.key and t2.value <> t1.value)