Link to home
Start Free TrialLog in
Avatar of Tech_Men
Tech_MenFlag for Israel

asked on

remove rows whit 0 result

hi there ,
this is my code :
i want to get only rows that the value of the colum Bal is large from 0
if the bal is 0 i want the select will pass over and no bring the row from the db
thanks ...
select WarehouseRunID,ProRunId,ProMkt,ProName,ProMinInStock,ProRem,SupName,
 (select Sum (MoveDepQuant) - sum(MoveWithQuant)) as Bal  from dbo.Products 
inner join dbo.Moves on ProRunId=MoveProRunId 
inner join dbo.Supplier on Products.SupRunId=Supplier.SupRunId
inner join dbo.Warehouse on MoveWhereHousRunId=WarehouseRunID
group by ProRunId,ProMkt,ProName,ProMinInStock,ProRem,SupName,WarehouseRunID

Open in new window

SOLUTION
Avatar of knightEknight
knightEknight
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
try this.
select * from (
select WarehouseRunID,ProRunId,ProMkt,ProName,ProMinInStock,ProRem,SupName,
 (select Sum (MoveDepQuant) - sum(MoveWithQuant)) as Bal  from dbo.Products 
inner join dbo.Moves on ProRunId=MoveProRunId 
inner join dbo.Supplier on Products.SupRunId=Supplier.SupRunId
inner join dbo.Warehouse on MoveWhereHousRunId=WarehouseRunID
group by ProRunId,ProMkt,ProName,ProMinInStock,ProRem,SupName,WarehouseRunID) t1
where Bal > 0

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Tech_Men

ASKER

thanks