Why Does My Query Sort Differently After I Upgraded to Sage 300 Construction and Real Estate (CRE) version 16.1?

Scenario

After upgrading to the Sage 300 CRE version 16.1 the Office Connector queries are not sorting by the table’s primary key order.

Explanation

Sage utilizes a Software Development Kit (SDK) made by Simba Technologies to create the Sage 300 CRE ODBC driver. With Sage 300 CRE v16.1, Sage is now using version 10.0 of the Simba SDK (version 7.7 was used previously). This upgrade was done in order to leverage new capabilities offered by the newer SDK. In some cases, this also results in the Sage 300 CRE driver behaving differently than before.

In prior versions, data was returned in Primary Key order. In 16.1, the data is not returned in primary key order unless you specify an order (ORDER BY) clause — without an ORDER BY clause in the query, the ODBC driver will return data in the order in which it is able to retrieve it most efficiently. In many cases, this will not be the desired order. If you want the same sort behavior as prior versions, you need to explicitly name the sort fields in the order you want them sorted.

Resolution 1

Sort the Office Connector Query using Excel’s Sort Command. After the data is sorted using this command then Excel will keep the sort order persistent in the subsequent refreshes.

1. Make sure that the active cell is in the table containing the data.
2. On the Data tab, in the Sort & Filter group, click Sort.
   • 
3. In the Sort dialog box, specify all the Sort by columns, Sort On, and Order for each sort level.
   • 
4. Click OK.

Resolution 2

Modify the existing SQL statement by adding the ORDER BY clause. A license of Office Connector Query Designer will be required.

1. Make sure that the active cell is in the table containing the data.
2. On the ADD-INS tab, click the SQL Editor Command button on the Office Connector toolbar.
3. Add the ORDER BY clause to the very end of the resulting SQL statement. The fields are comma separated.
   • Example 1 - Sorts by the Job, Extra, Cost Code and then Category:
     
   • Example 2 - Sorts by the Job, Extra, Cost Code and then Category all in descending order with the DESC keyword after the field name:
     
   • Example 3 - In a UNION or UNION ALL statement the ORDER BY must use the column number in the SELECT clause instead of the field’s name. The following example ORDER BY statement sorts by Job, Extra, Cost Code and Category with a UNION statement:
     

Note that when sorting segmented Job Numbers, Cost Codes and GL Account numbers, a standard ORDER BY clause will cause the numbers to sort incorrectly. For example, the cost code list may sort 1-040, 1-045, 10-010, 10-015, 2-020 and so forth. To correctly sort these values in the correct order the use of scalar functions is required instead of just the field name. Here are some examples:

• Job Number - RIGHT(CONCAT(SPACE(12), "JOB"), 12)
• Cost Code - RIGHT(CONCAT(SPACE(15), "PHASE"), 15)
• GL Account - RIGHT(CONCAT(SPACE(29), "AACCT"), 29)
  •