Logical functions provide a way to use logical conditions to display one of several values. You can use logical functions to test a field’s value such as gender (M or F) and display another value(‘Male’ or ‘Female’) as a result.
In this article we describe how to use the CHOOSE and IIF functions. CHOOSE is really a great way to pick one value from a list of indexed values, whereas IIF provides a compact means to provide the same type of conditional testing found with in the CASE statement.
If you not familiar with SQL functions, then I would recommend staring with the Introduction to SQL Server Built-In Functions.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.
Introduction to SQL Server’s Built-In Logical Functions
The IIF and CHOOSE functions are new to SQL Server 2012. They allow you to perform comparisons within the select statement to decide which of several values to return.
CHOOSE
The Choose function is used to select a value from a list. It is really handy when you have a numeric value that represents a position, such as the numbers 1 through 12 represent the months of the year, and you want to use them to “map” to another value, such as seasons.
The format for chose is
CHOOSE(index, value list)
Given the index, which is a positive number, the CHOOSE function returns the corresponding entry from the value list.
For example
SELECT CHOOSE(2, 'A', 'B', 'C')
Returns B
You can use non integer values, such as FLOAT and DECIMAL, as well. They are implicitly converted to their integer value. Thus
SELECT CHOOSE(2.9, 'A', 'B', 'C') Also returns B since 2.9, when converted to an integer, is 2.
Use CHOOSE to Map Date to Season of Year
Suppose the Adventure Works hiring manager wants to know the season each hire occurs? What can be done? By using the number of the month, we can use the CHOOSE function to get the season. Here is the CHOOSE function we can use:
CHOOSE(MONTH(HireDate),
'Winter', 'Winter', 'Winter',
'Spring', 'Spring', 'Spring',
'Summer', 'Summer', 'Summer',
'Fall', 'Fall', 'Fall')
MONTH, is a DateTime function, that returns a number 1-12 which corresponds to the month; January corresponds to 1 and December to 12.
If the HireDate is in October, MONTH is then 10, resulting in ‘Fall,’ the tenth element in the list, to be returned
Here is the SQL we can use to obtain the hiring manager’s request:
SELECT NationalIDNumber,
JobTitle,
MONTH(HireDate) as HireMonth,
CHOOSE(MONTH(HireDate),
'Winter', 'Winter', 'Winter',
'Spring', 'Spring', 'Spring',
'Summer', 'Summer', 'Summer',
'Fall', 'Fall', 'Fall') as [Hiring Season]
FROM HumanResources.Employee
One thing to point out is NULL is returned if the CHOOSE function index is less than one or greater than the number of items in the value list.
SELECT CHOOSE(0, 'A', 'B', 'C')
Returns NULL since the index is less than one, and
SELECT CHOOSE(4, 'A', 'B', 'C')
does as well, since the index value of 4 is greater than then number of items in the value list.
Here is another example.
Use Choose to Map Level to Title
Suppose the hiring manager wishes to each employee and a description of their level within the organization. You’ll notice each employee is assigned a level
The organization levels start at the top with 0 and progress from there. We can use the CHOOSE function to create a mapping between the organization level and description. Since
the organization level starts with level starts with 0, we need to add 1; otherwise the first level (0) would always return NULL. Remember, only index values greater than or equal to one are allowed.
After taking this into account the SQL we can use is:
SELECT NationalIDNumber,
JobTitle,
OrganizationLevel,
CHOOSE(OrganizationLevel+1,
'Executive', 'Executive',
'Upper', 'Middle',
'Lower', 'Lower') as [Organization Level Name]
FROM HumanResources.Employee
Though our value lists have returned textual results, there is not reason it couldn’t be another datatype, such as a DATETIME or a numeric value.
The CHOOSE function is a really good fit when you which to select a value based on another. I think if value list is too large though you may want to reconsider using another means to map. For instance you could create a table to serve as a lookup. Keep in mind that CHOOSE is “converted” to a CASE statement, so if you think a CASE statement “smells,” then surely CHOOSE will also stink!
IIF Logical Function
The IIF statement is used to return one of two results based on the result of a Boolean condition.
Here is the general format of the statement
IIF ( Boolean expression, true value, false value )
IIF is shorthand for “inline if.” If the Boolean expression is TRUE, then the true value is returned else the false value is returned.
The statement
SELECT IIF(10 > 100, '10 is weird', '10 is less than 100')
Returns ’10 is less than 100′ since the Boolean expression “10 > 100” is FALSE.
Here is a example using IIF
SELECT NationalIDNumber,
IIF(Gender='F','Female','Male')
FROM HumanResources.Employee
Here are the results
Nesting IIF Statement in SQL
You can nest IIF statements. For instance
SELECT NationalIDNumber,
Gender,
MaritalStatus,
IIF(Gender='F',IIF(MaritalStatus='S','Single Female', 'Married Female'),
IIF(MaritalStatus='S','Single Male', 'Married Male'))
FROM HumanResources.Employee
Returns the following values
In this example the first IIF test for Gender. If the Gender is ‘F’ then the green IIF (the one corresponding to the true value position) is evaluated; otherwise the IIF located in the false value position is evaluated.
Though the above example works, it is hard to read. Before we move on can you think of a better way to write the expression?
Here is what I came up with…
SELECT NationalIDNumber,
Gender,
MaritalStatus,
IIF(MaritalStatus='S','Single', 'Married') +
' ' +
IIF(Gender='F','Female', 'Male')
FROM HumanResources.Employee
I think this is easier to read. The lesson to take from this is that though you can nested IIF statements, they are harder to read, and if you can, look for another way to write the expression.
In fact the IIF function is a shortcut for a CASE statement. Here is the same example as a case statement.
SELECT NationalIDNumber,
Gender,
MaritalStatus,
CASE
WHEN MaritalStatus = 'S' Then 'Single'
ELSE 'Married'
END +
' ' +
CASE
WHEN Gender = 'F' Then 'Female'
ELSE 'Male'
END
FROM HumanResources.Employee
Though nested IIF’s are harder to read, I think they are really useful when the expressions are simple. They are compact; however, if your tests are complex or more than two, which lends itself to nesting IIF’s, then I would tend to use a searched CASE statement.
Leave a Reply