AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

The iif() and switch() functions with two simple conditions

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


Using the iif() and switch() functions with two simple conditions

Produce a product catalog with special discounts for one product only

Discussion:

We received an urgent message from the inventory department saying that we have a large quantity of Chocolate Fudge which needs to go out into the market fast.  We only have a few hours to produce a new product catalog for this week and send to our customers.  All of the product prices in the catalog should remain the same except for Chocolate Fudge, which will be reduced by 50%.  We must not make this percentage change right in the table because this price will be valid only for a week. 

Solution 1: using the iif() function

We can solve the problem above using an iif() function.  The translation of the iif() function in this example says:  For the product with productid = 14, change the value of the field ProductUnitPrice by 50%, and for the rest of the products in the catalog, leave the price as is in the table.  In addition, notice that a second function is used, cCur(), to display numbers with the dollar sign in front of them.  Notice also that we changed the title of the ProductUnitPrice field to appear as "ProductPrice" in this week's catalog.  Finally, notice in the result set that the price of Chocolate Fudge is now $20.50 in the query, while it remains $41 in the products table. 

Code:

SELECT ProductID, ProductName, QuantityPerUnit, cCur(iif([productid]=14,[productunitprice]*0.5,[productunitprice])) AS ProductPrice

FROM Products
 

Result:


Solution 2: using the switch() function

There is no need to use the switch function in this case since the conditions are very simple.  However, I would like to show you its syntax and how it works with respect to an iif() function.  In this example, we again used the Ccur() function in front of the switch function to obtain the dollar sign in front of the product prices.  There is no doubt that the switch() function is much cleaner and comprehensible than the iif() function.  This becomes more evident as the number of conditions goes up. 

Code:

SELECT ProductID, ProductName, QuantityPerUnit,

SWITCH(

productid = 14,         productunitprice*0.5,

productid <>14,        productunitprice

)

AS ProductPrice

FROM Products
 

Result: