T-SQL 2012: CHOOSE and IIF

 

We’ve been needing these two features for a long time. This post includes a couple of easy examples showing how these functions work.

CHOOSE

I’ll start with CHOOSE. By using CHOOSE you can do away with many tedious CASE statements. CASE statement opportunities are everywhere in Dynamics GP. For instance, how many times have you needed to display the document type in your report and want a word, not a number. Prior to SQL 2012, we would have written a CASE statement that would look a lot like this:

Case SOPTYPE
    when 1 then 'Quote'
    when 2 then 'Order'
    when 3 then 'Invoice'
    when 4 then 'Return'
    when 5 then 'Back Order'
    when 6 then 'Fulfillment Order'
    ELSE 'Undefined Document Type'
End as ' Doc Type'

CHOOSE will pick the value in your list according to the value of your data. It returns the expression at a specific index. So, instead of writing a case statement, you can simply write this:

CHOOSE (SOPTYPE, 'Quote', 'Order', 'Invoice', 'Return',
'Back Order', 'Fulfillment Order') AS DocType

If the value of your index is greater than the number choices, it will return NULL. It will also return NULL if the value is less than 1.

IIF

Next is IIF. Most of you probably already know how to use this and have wondered why you couldn’t do it in T-SQL. Flavors of this are in Excel formulas, Access calculations and even VBA, to name a few. It works with a Boolean expression and returns a value based on whether the expression is true or false. You set this up with three parameters. The first parameter is the expression you’re evaluating, the second parameter is the value if the expression is true, and the third parameter is the value if the expression is false. Yep, just like Excel.

So here’s what it looks like:

IIF(boolean_expression), value if true, value if false)

So much easier than CASE.

Until next post!

Leslie