SQL Server Performance

use calculated field in where clause

Discussion in 'T-SQL Performance Tuning for Developers' started by boutwater, Feb 19, 2008.

  1. boutwater Member

    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]
  2. ndinakar Member

    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..
  3. Adriaan New Member

    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)

Share This Page