3305 Main Street Ste 019 Vancouver, WA 98663

Queries may not return correct results when conditioning on a nullable column 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.

Some columns in the database may contain NULL values. In the legacy database, when nullable columns were used in conditions (in the WHERE clause), NULL values were treated the same as a blank or zero. With the Microsoft SQL Server ODBC driver, comparisons involving NULL values result in a NULL result (or false), meaning that the condition is not satisfied.

Symptom

Queries return the wrong set of records (too few or too many).

Solution

When using nullable columns as part of conditions in the WHERE clause, use the ISNULL function to convert NULL values to values that can be compared.

Before Correction

SELECT   recnum AS Job,   jobnme AS Job_Name,   sprvsr AS Supervisor FROM   actrec WHERE   sprvsr<>8 ORDER BY   jobnme

Excel:

ABC
1JobJob NameSupervisor
2222Big Redwood Tree Hotel4
3201Trappen Motel52
4186Williams Post Office7
5207Wood Elementary School6

After Correction

SELECT   recnum AS Job,   jobnme AS Job_Name,   ISNULL(sprvsr,0) AS Supervisor FROM   actrec WHERE   ISNULL(sprvsr,0)<>8 ORDER BY   jobnme

Excel:

ABC
1JobJob NameSupervisor
2222Big Redwood Tree Hotel4
3221Bike Path for Rte 660
41Historical Job Costs0
5220Lamb Shoes0
6223Main Street Post Office0
7201Trappen Motel52
8224Walking Shoes Shop0
9186Williams Post Office7
10207Wood Elementary School6

Notice that the nullable field (sprvsr) has been wrapped with an ISNULL function so that any NULL values will be reported as a zero. Notice that after the correction, the query rows where the sprvsr column is not 8 and also not NULL.

At a future date (relative to this article's pushing) the insertion of the ISNULL function will be automatically performed by Liberty Reports.


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