In needing to write a small program for my company access data from our Sage 100 ERP I found documentation lacking concerning writing SQL commands for the Sage 100 ODBC driver. Here are are some commands that I found useful when writing SQL statements for Sage 100.

  • When working with SQL and ODBC it’s nice to have a program where you can test your SQL statements. I use WinSQL which can be found here WinSQL Lite

Since I am using the ODBC driver with VB.NET and Visual Studio It would be good or me to mention that the ODBC driver documents three data types. Numbers, Dates, and Strings. Based on my experience if a datatype is not declared then it assumes a string.

The basic of the ODBC driver is similar to MSSQL or MySQL syntax with few executions. A basic example could be:
SELECT TOP 10 * from CI_Item
In the following example I select the top 10 rows from all the columns for the CI_Item table of Sage 100. I use the top feature for testing SQL statements to make sure the basic function works.
To select specific columns replace the * with he columns from Sage separated by commas. The following examples will show how to write out basic SQL statements also include is a User Defined Field or UDF so you can see how your custom fields can be accessed.
SELECT TOP 10 ItemCode,ItemType,UDF_UPC_CODE FROM CI_Item

The example above yields the following results:
ItemCode ItemType UDF_UPC_CODE ----------------------------------------------------- /01 5 NULL /02 5 NULL /03 5 NULL /04 5 NULL /05 5 NULL /06 5 NULL /07 5 NULL /08 5 NULL /09 5 NULL /10 5 NULL

As you can see from example that the information is not very useful. The top 10 from my statement is just miscellaneous items. And the UPC codes are NULL. So let’s say you don’t want to see the miscellaneous items to fix this use the WHERE to limit the results.
SELECT TOP 10 ItemCode,ItemType,UDF_UPC_CODE from CI_Item WHERE ItemType = '1' If you are familiar with SQL this should be familiar. The results from this are as follows. Actual SKU’s and UPC Codes have been obfuscated. As another note this article is not about sorting so although my example is sorted millage may vary.
ItemCode ItemType UDF_UPC_CODE ----------------------------------------------------- 0.120 1 NULL 0.121 1 NULL 0.123 1 NULL 0.124 1 800000000000 0.125 1 NULL 0.126 1 800000000001 0.127 1 NULL 0.119 1 800000000002 0.112 1 NULL 0.114 1 800000000003
As you can see from this example the addition to the WHERE clause as limited the results to just show item types of 1 excluding all the types. I can take this one step further my limiting the results so it does not show any NULL values.
SELECT TOP 10 ItemCode,ItemType,UDF_UPC_CODE from CI_Item WHERE ItemType = '1' AND UDF_UPC_CODE IS NOT NULL
In this example result will will only show the top 10 results that have an item type of 1 and a UPC code that is not NULL. The result will look something like this:
ItemCode ItemType UDF_UPC_CODE ----------------------------------------------------- 0.124 1 800000000000 0.126 1 800000000001 0.119 1 800000000002 0.114 1 800000000003 0.300 1 800000000004 0.440 1 800000000005 0.442 1 800000000006 0.500 1 800000000007 0.550 1 800000000008 0.502 1 800000000009

In the next article I will go through some of the more complex SQL statements where the ProvideX ODBC driver diverges from more normative SQL syntax.

Comments

Commenting is closed for this article.