help required in simple select query

• khurram sultan

  SSC-Addicted

  Points: 485

  More actions

  November 20, 2009 at 6:38 am

  #156420

  SELECT Vw_Final_Inventory_Report_1.*,

  (select sum(Dqtyin) - sum(Dqtyout) from Tb_Item_Cost_Ledger where itemid = Vw_Final_Inventory_Report_1.coa_id and VDate < '2009/01/01') as ClosingBalance

  FROM Vw_Final_Inventory_Report_1

  this query works fine

  but when i try to input where clause it gives error as following

  SELECT Vw_Final_Inventory_Report_1.*,

  (select sum(Dqtyin) - sum(Dqtyout) from Tb_Item_Cost_Ledger where itemid = Vw_Final_Inventory_Report_1.coa_id and VDate < '2009/01/01') as ClosingBalance

  FROM Vw_Final_Inventory_Report_1

  where (ClosingBalance = 0 )

• Old McDonald

  SSCertifiable

  Points: 5768

  More actions

  November 20, 2009 at 6:56 am

  #1082182

  You can not use derived column names in the WHERE clause.

  Try following query:

  SELECT Vw_Final_Inventory_Report_1.*

  ,(sum(Dqtyin) - sum(Dqtyout)) AS ClosingBalance

  FROM Vw_Final_Inventory_Report_1 vw

  INNER JOIN Tb_Item_Cost_Ledger tb

  ON tb.itemid = vw.Vw_Final_Inventory_Report_1.coa_id

  WHERE VDate < '2009/01/01'

  AND (sum(Dqtyin) - sum(Dqtyout)) = 0

  -Vikas Bindra