I was recently preparing a query for a report where the client needed to find out the following information from a small table -
- Products that have been Discontinued but are receiving Orders
- Products that have not been Discontinued but do not generate Orders
I solved this requirement using Conditional WHERE Clause. The Products table of the Northwind database has fields that match this requirement and I will demonstrate the Conditional WHERE clause query using the same. Here’s the query:
SELECT ProductID, ProductName, UnitsOnOrder, Discontinued from
(Discontinued = 1 AND UnitsOnOrder > 0)
(Discontinued = 0 AND UnitsOnOrder = 0)
You can also branch the statements and re-write the query using an IF-ELSE statement, however since this was a small table, I went in for the Conditional WHERE clause.