In my post SQL Query Fundamentals (Part 1) I covered a number of "fundamental" elements of SQL Query Design:
- Select statements
- Aliases
- Cases
- Casting
- Converting
- Table Joins
- Where Clauses
This post is intended to amplify and expand on these concepts to help resolve common problems with Query Design for practical purposes in concert with Dynamics GP.
Dynamics GP is designed and built to support exceedingly large business with massive transaction loads. Part of this architecture is the use of Work, Open and Historical tables to house data. Typically Work Tables contain transactions being entered, which have not been posted. Open tables contain transactions which have been posted in the current (Open) fiscal or calendar year. Historical tables contain transactions from past (Closed) fiscal or calendar years.
Dividing data into these categories and storing the data in different tables improves performance by ensuring users entering and accessing these various data types are not competing for the same resources.
As a consequence of this design and implementation, in order to produce a report, which contains all transaction data, you must combine the data from these various tables. This is not the same thing as a Join. Typically, a join is intended to link tables together, so you can present additional data about the records you are interested in, like the address records associated with customer or vendor records.
When you desire to present data from two tables containing the same fields in a different state, like open vs. historical data, you do not use Left, Right, Inner or Outer Joins. You use Union statements. In order to use a Union statement, the columns for the two tables must "constructively" match - there must be the same number of columns with the same data types.
Here's an example:
Now back to fundamentals.
First, you will note there is some Green text in the first line of this query. This text has been commented out, and is functionally invisible to SQL. This can be accomplished by beginning a single line with two hyphens (--). Multiple lines can be commented by starting with /* and ending with */.
I typically use comments liberally in queries, and you should develop this habit too. It is invaluable when coming back to a query later, especially much later, to know what you were thinking when you designed it. It is also helpful to comment the mechanisms, and purposes of joins, details about the tables and fields involved. This is especially useful in environments where you are part of a team.
You will notice the first column - Source, identifies the data source of a particular record. Using this simple feature is absolutely critical when presenting data from multiple tables. You simply quote the data source and alias the column. In this case the sources were Inventory and Manufacturing tables which contain similar data. In other cases it may be Open and Historical. In either case, adding a column to denote the source, makes it easy to identify the source of the data.
The cost columns have a new Cast statement, which ensures they are presented in Money format (currency).
You may also notice there is a new kind of join in this statement - a Left Join. Left Joins are handy for adding fields from tables, where a value may or may not exist for the joining records. Inner Joins, require the presence of matching data, so using an inner join instead of a Left Join in this case, could restrict the records returned. If there is not a matching data point in the table you used a Left Join to connect, you will see NULL in the field. If you used an Inner Join, you would suppress the entire row, where a NULL value occurs.
Because this post is Dynamics GP specific I will explain the reason a left join was necessary. Since Dynamics GP can have a 66 character account number with up to 10 segments, and accounts are used in numerous places in Dynamics GP, Great Plains uses an Account Index instead of storing the whole account. This data is stored in the GL00100 table in the ACTINDX field (GL Master Records). More importantly, it is stored in the GL00105 table, which also includes the ACTNUMST field; Account Number String field. By Left Joining the GL00105 table, it is possible to include the actual account number in the query results.
Unlike JOINS, UNION statements connect tables without requiring the presence of matching values in fields. You could literally UNION queries of like fields in your Vendor and Customer tables. There are two types of UNION statements; UNION and UNION ALL. UNION queries return unique values, and UNION ALL queries will include duplicate records, so if you want all the data, you'll need to use a UNION ALL statement.
In this case, Dynamics GP holds Standard Cost Revaluation Records in both Inventory and Manufacturing tables, because there are two tools to Revalue Standard Costs. In order to display pending Standard Cost changes, it is imperative to present the data from both sources.
Finally, the last line in this query is an Order by statement. Order by statements instruct the query to return the data in a particular order. Simply type Order by, and then specify the columns you want to sort your data by, separating them by commas. The default sort order is Ascending. You can specify the sort order by typing ASC or DESC (short for Ascending and Descinding) after the column name.
Hopefully these posts have helped speed you on the way to learning SQL Query Fundamentals.
