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 within the CASE
statement.
If you are 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 choose
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.
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.
How Choose Works
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 the number of items in the value list.
Here is another example.
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 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 no 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 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:
Using IIF to Display Descriptive Values.
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:
Using Nested IIF’s to Display Results
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.
Nested IIF Execution Order
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 have 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.
The post Introduction to SQL Server’s Built-In Logical Functions appeared first on Essential SQL.