3305 Main Street Ste 019 Vancouver, WA 98663

Some GROUP BY queries fail after upgrading from Sage 100 Contractor v19 to v20

Background

The upgrade from Sage 100 Contractor v19 to v20 represents a major change to the underlying database. Beginning with v20, Sage 100 Contractor now uses a Microsoft SQL Server database. Because of this, the database driver (which provides the connectivity between Liberty Reports and your Sage 100 Contractor data) is different and has different capabilities and limitations.

When using the DISTINCT keyword in a query, the legacy database would permit columns to be included in the ORDER BY clause that were not output columns (in the SELECT list). The Microsoft SQL Server ODBC driver enforces this requirement. When the DISTINCT keyword is specified, the only columns that can be included in the ORDER BY clause are those that are listed as output columns (in the SELECT list). When building a GROUP BY query using the Liberty Reports query wizard you are only allowed add columns to the ORDER BY clause that are already included in the SELECT list. If the ORDER BY clause contains other columns, the SQL text was manually edited.

Symptom
  • Symptom 1 - A popup message is displayed indicating that one of your queries could not be refreshed. If you right-click the range of cells containing the query and click Refresh Data, the following message is displayed:
    [Microsoft][ODBC SQL Server][SQL Server]Column 'column-name' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
  • Symptom 2 - A Liberty Reports function (such as DBLookup or DBFind) does not return any value and the following message is written to the log:
    [Microsoft][ODBC SQL Server][SQL Server]Column 'column-name' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause..
Solution

Edit the SQL text of the query (or the SQL text being passed to the Liberty Reports function) and remove columns from the ORDER BY clause that do not appear in the SELECT list. Example -

Before Correction

SELECT   vndnum AS Vendor,   SUM(invbal) AS Invoice_Balance FROM   acpinv GROUP BY   vndnum ORDER BY   vndnum, invnum 

After Correction

SELECT   vndnum AS Vendor,   SUM(invbal) AS Invoice_Balance FROM   acpinv GROUP BY   vndnum ORDER BY   vndnum

Notice that the highlighted text has been removed in the corrected version because the invnum column is not in the SELECT list above.


See Also -
Common issues with custom reports after upgrading from Sage 100 Contractor v19 to v20