Understanding SELECT Statements

The first time anyone looks at SQL can be overwhelming.  Mostly because it is like learning a foreign language.  You need to learn the vocabulary and the syntax to get what you want.  The first step to learning should be how to navigate information.  In my examples, I will be using examples from Microsoft Dynamics GP 2015’s database.  I will also be using the table PM00200 (Vendor Master) for all Select queries for my examples.

The simplest concept within SQL querying is the select statements.  Once you understand the concept you would be able to apply the select statements to all types of databases.  It is important to note that a select statement does not change data in any shape or form.  Select statements are simply a method to pulls data in a way that is simpler to analyze.

The vocabulary that we will be using in this post are:

SELECT – Pulls rows and columns from the database
FROM – Specifies the specific table or view.  Always used in conjunction with a SELECT, DELETE, and UPDATE statement.
WHERE – Specifies additional conditions for a SELECT, DELETE, or UPDATE statement.
GROUP BY – Specifies which columns you want to have information grouped by.
ASC – Is used with GROUP BY to place items in an ascending order.
DESC – Is used with GROUP BY to place items in a descending order.

Let’s start with the basics.  A simple select statement.  All select statements have similar components.  If you have questions about navigating the SQL query windows please see my post Understanding SQL Query Window.

  • They all start with the word SELECT.
  • Then they specify the columns. If you want all columns in a table to appear then you would select an asterisk (*).  If you want specific columns, then you would select the column name and separate then with a comma.
  • Then you would use the word FROM to specify where the columns are located.
  • Then you will type the column name.

Using my example for this you would type the following to return all columns

SELECT * FROM PM00200

sql2-1

This returns all the columns.  Let’s say we only want the vendor ID, Vendor Name and their state.  We would then take the column names and replace the asterisk.

SELECT VENDORID, VENDNAME, STATE FROM PM00200

sql2-2

Now let’s say we only want to see the vendors for the state of Illinois.  To accomplish this we would add in the WHERE clause.

SELECT VENDORID, VENDNAME, STATE FROM PM00200 WHERE STATE=’IL’

sql2-3

Please note that the single ‘ sometimes does not copy and paste correctly into the query screen and you will need to delete and retype this into the query window.  Adding ASC or DESC to the end will change how the order goes.

sql2-4

SELECT VENDORID, VENDNAME, STATE FROM PM00200 ORDER BY STATE ASC
SELECT VENDORID, VENDNAME, STATE FROM PM00200 ORDER BY STATE DESC

ASC is the abbreviation for ascending.  DESC is for descending.  Meaning ASC will place the items in alphabetical (ascending) order and DESC will place the items in reverse alphabetical (descending) order.

Advertisements

Useful KB Articles

Here is a list of KB articles I reference.  I have these bookmarked and have found them to be extremely useful.

General:
Microsoft Dynamic GP Directory – Quickest way to locate updates and year end information
888003 – Year-end closing procedures for General Ledger
864652 – The checkbook balance and the general ledger cash account do not balance

Bank Reconciliation
872693 – Description of Bank Reconciliation tables
851301 – Error message when you reconcile the checkbook in Select Bank Transactions in Microsoft Dynamics GP: “The difference must be zero before you can reconcile this checkbook”
857211 – How to start using an existing checkbook to reconcile in Bank Reconciliation
866167 – Description of the transaction flow in Bank Reconciliation

Electronic Bank Reconciliation
2872105 – Steps to modify the BAI file format in Electronic Reconcile
850751 – Setup information for Electronic Reconcile Format Configuration

Payables:
858279 – Payment Date on the Payables Historical Aged Trial Balance
936280 – Return transaction document types

New GP Company:
866332 – Copying chart of accounts from one company to another using Microsoft SQL Server
872709 -How to copy setup tables from one company to another