Technical Airlift Here We Come! SQL Tips and Tricks

Hopefully we will see some of our ardent followers at the upcoming Microsoft Technical Airlift for Dynamics GP in Fargo, ND later this month. Steve and I are presenting a session on SQL tips and tricks for consultants. Definitely not a developer-oriented session, we hope to help front line consultants and support folks build our their SQL toolboxes to up their game in terms of queries, reporting, and data fixing.

 It has been fun working on the presentation, and I thought I would share some of the fun things we have come across in a few posts over the next couple of weeks. Here are a couple things from the Cool SQL Keywords section of the presentation...

Much like a middle child, BETWEEN is often forgotten although it saves us precious keystrokes and is so simple to understand. It can be used in place of using ">=" and "<=" in a WHERE clause.

For example,
 "select * from RM00103 where CUSTBLNC >=10000 and CUSTBLNC <=100000"
Becomes...
"select * from RM00103 where CUSTBLNC BETWEEN 10000 and 100000"

So clear cut, right?

Now, the second on we chatted about a bit was UNION vs UNION ALL. Now, the little know fact is that UNION actually causes duplicates to be eliminated. In reality, we use UNION a lot to marry up open, history, and maybe work tables. So we wouldn't expect to have any duplicates, right? But it is worthwhile to know this, as it may cause an issue if you are data fixing and resolving duplicate issues...or don't realize you have some.

So in that case, you may want to err on the conservative side and use UNION ALL which does not eliminate duplicates. Simple enough...

SELECT * FROM PM20000 UNION ALL SELECT * from PM30200

We will share more as it gets closer, and if you are coming to Fargo stop by our session and say Hi!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.