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

T-SQL 2012: LAG & LEAD

I’ve just started playing with SQL 2012 and wanted to show you the new LAG and LEAD features.

I’ll use the report below as an example. Suppose you needed to generate a report that showed the last three years of sales. The result set would need to look something like this:

LEAD looks ahead the specified number of rows from the current row, and LAG (you guessed it!) looks behind the specified number of rows from the current row. By using the OVER clause you can specify your grouping (partitioning) and sort order.

Using SQL 2012 you can easily accomplish this using the following SELECT statement:

SELECT
   year(docdate)  AS ‘Order Year’
  ,SUM(SubTotal) AS ‘Current Yr Sales’
  ,LAG(SUM(SubTotal), 1, 0) OVER (ORDER BY YEAR(docdate)) AS ‘Last Year Sales’
  ,LAG(SUM(SubTotal), 2, 0) OVER (ORDER BY YEAR(docdate)) AS ‘Year Before Last’
  ,LAG(SUM(SubTotal), 3, 0) OVER (ORDER BY YEAR(docdate)) AS ‘Three Years Ago’
  ,LEAD(SUM(SubTotal), 1, 0) OVER (ORDER BY YEAR(docdate)) AS ‘Next Year’
FROM SOP30200
GROUP BY YEAR(DOCDATE)
ORDER BY YEAR(DOCDATE);

Have fun with it!

Until next post!

Leslie

Dexterity Training in Dallas (June 17 – 21, 2013)

 

Looking for Dexterity training? Come to Dallas!

We’re running this class if only a single person signs up, no cancellations here in Dallas .

I will be teaching the Dexterity Basics class in Dallas, Texas from June 17^th to June 21^st, providing the basic foundations any Dexterity programmer must have.

This is an opportunity for all of you VSTools programmers to learn how to build your user interface in Dexterity instead of WinForms so that your application will run with the new GP 2013 Web Client. Your existing code will hook right up to the Dexterity events, you may even like the Dexterity forms designer better than VS Tools!

Dexterity is easy to learn and easy to maintain.

For you VBA enhancers, you will learn how to write Dexterity code to deliver your solution instead of VBA. While VBA works marvelously with the rich client, it will not work for the new GP 2013 Web Client. Come to Dallas and learn how easy Dexterity really is.

Even you Technical Consultants, taking the basic Dex class with provide you with the tools you need to understand what all of those error messages really mean. You can become better consultants by learning just how this engine works! (and it’s easy).

For more information, send an email to training@conexussg.com or call 469-828-3274.

Hope to see you in Dallas!

The basics class covers the following topics:

Dexterity I – Product Fundamentals

Synopsis:
Dexterity Product Fundamentals will teach you about the development environment used to build the Microsoft Dynamics GP family of products. This course will provide you with the foundation you need to build integrated, database-independent applications. In addition, you will learn how to provide powerful application functionality for Microsoft Dynamics GP.
By actively participating in these classes, you should learn:

Basic programming techniques used by Microsoft Dynamics GP to develop their core modules.

• How to modify existing Dynamics GP forms and reports in order to make customizations for customers.
• How to develop add-on applications that will interact with the Dynamics GP products to meet specific requests by customers.
• How to package and ship your dictionary allowing Microsoft Dynamics GP to make updates to Dynamics GP without affecting the developer’s dictionary.
• How to register your product with Microsoft Dynamics GP using a unique Product ID to ensure no duplication of add-on applications with the same name.
• How to develop add-on applications that will “look and feel” like Dynamics GP and provide seamless interface between the applications to ensure a professional finished product.
• How to use object triggers, which are scripts in your applications that are invoked by events in Dynamics GP, allowing several developers to deliver enhancements to the same Dynamics GP form.

Kofi Annan Convergence 2013 Keynote Speaker

 

 We had the honor of hearing the wisdom of Kofi Annan as the keynote speaker on the Las day of Convergence. Here is a little more information about this Nobel Peace Prize awarded. 

 

<!--[if !vml]--><!--[endif]-->

Kofi Annan

The Nobel Peace Prize 2001 was awarded jointly to United Nations (U.N.) and Kofi Annan "for their work for a better organized and more peaceful world"

Photos: Copyright © The Nobel Foundation

 

 

MLA style: "The Nobel Peace Prize 2001". Nobelprize.org. 21 Mar 2013 http://www.nobelprize.org/nobel_prizes/peace/laureates/2001/

Biography

Kofi A. Annan of Ghana, the seventh Secretary-General of the United Nations, is the first to be elected from the ranks of UN staff. His first five-year term began on 1 January 1997 and, following his subsequent re-appointment by the UN Member States, he will begin a second five-year term on 1 January 2002.
MLA style: "Kofi Annan - Biography". Nobelprize.org. 21 Mar 2013 http://www.nobelprize.org/nobel_prizes/peace/laureates/2001/annan.html


He is truly a remarkable man.


Until next post.

Leslie

Volunteer Day at Convergence 2013

Sheila-Jefferson Ross participated in the volunteer project and gave me these great pictures.

		Volunteer Day at Convergence 2013 VIEW SLIDE SHOW DOWNLOAD ALL

Thanks Sheila! Keep up the good work.

Until next post!

Leslie