Introduction to SQL Server’s Built-In Logical Functions

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.

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:

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 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 IIFs 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 IIFs, 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.