Supressing Division by Zero in the Where Clause

Rampage-Obidos

When running this query I get the Division by Zero error come up, I know that is correct, however I would like it to error to zero if possible.

I have attempted using CASE, and it doesn't seem to allow it to be used in the WHERE clause. This is why I have moved the math to the WHERE part of the query in order to limit the results.

WHERE 100*[StockQTY1]/[ShelfCapacity1]<'$ShowPercent'

Full query:

SELECT [Season],[PLU],[ProductDescription],[SupplierCode],[StockQTY1],[StockQTY3],[ShelfCapacity1],[StockQTY2],[StockRoomLocation],[StockRoomLocation2],[SalePrice1],[StockTakeCount1],[TempStockRoomLocation3],[StockTakeCount2],[StockTakeCount3],[TempStockRoomLocation2] 
FROM [PLU] 
LEFT JOIN [Stock] ON PLU.PLU = Stock.StockID 
WHERE 100*[StockQTY1]/[ShelfCapacity1]<'$ShowPercent' 
AND StoreID='$storeID' AND ([StockQTY2]>0 OR [StockTakeCount2]>0) AND (([StockQTY1]<[ShelfCapacity1] OR [StockQTY1]='0') OR ([StockTakeCount1]<[ShelfCapacity1] OR [StockTakeCount1]='0')) AND [DeptID] LIKE '$ShowDepartment'$SeasonShow$NotLikes 
ORDER BY [TempStockRoomLocation2] ASC, [StockRoomLocation] ASC, [SupplierCode] ASC

Any help would be very much appreciated.

Thanks

dagon

tried IF() ?

Rampage-Obidos

I have attempted to add the IF function, but I can't get it to work. Though I'm not sure it is going to help. The CASE function seemed to be ideal for me.

SELECT CASE WHEN [StockQTY1]=0 OR [ShelfCapacity1]=0 THEN 0 ELSE 100*[StockQTY1]/[ShelfCapacity1] END AS [Percentage],

However I am unable to then do this:

WHERE [Percentage]<'$ShowPercent'

The above WHERE statement simple returns an invalid column name error. Which I was expecting as it isn't a column, but I would like to be able to thin down my results with that variable.

Hopefully that explains the issue better.

Thanks