3305 Main Street Ste 019 Vancouver, WA 98663

Union queries containing parameters 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.

The legacy database supported the use of parameter markers (?) in the WHERE clause within UNION queries. The Microsoft SQL Server ODBC driver does not support parameters in this context.

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]The multi-part identifier 'column-name' could not be bound.
  • 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]The multi-part identifier 'column-name' could not be bound..
Solution

Wrap the UNION query by placing it in a subquery of the FROM clause. Then move the elements of the WHERE clause that use parameters into the WHERE clause of the outer query.

Before Correction

SELECT DISTINCT   bdglin.cstcde AS Cost_Code FROM   bdglin   LEFT OUTER JOIN cstcde ON bdglin.cstcde = cstcde.recnum WHERE   bdglin.recnum=?   AND bdglin.cstcde<>0 UNION SELECT DISTINCT   sbcgln.cstcde AS Cost_Code FROM   sbcgln   LEFT OUTER JOIN prmchg ON sbcgln.recnum = prmchg.recnum   LEFT OUTER JOIN cstcde ON sbcgln.cstcde = cstcde.recnum WHERE   prmchg.jobnum=?   AND prmchg.status NOT IN (5,6)   AND sbcgln.cstcde<>0

After Correction

SELECT DISTINCT   Cost_Code FROM   (     SELECT       bdglin.recnum AS Job,       bdglin.cstcde AS Cost_Code     FROM       bdglin       LEFT OUTER JOIN cstcde ON bdglin.cstcde = cstcde.recnum     WHERE       bdglin.cstcde<>0     UNION     SELECT       prmchg.jobnum AS Job,       sbcgln.cstcde AS Cost_Code     FROM       sbcgln       LEFT OUTER JOIN prmchg ON sbcgln.recnum = prmchg.recnum       LEFT OUTER JOIN cstcde ON sbcgln.cstcde = cstcde.recnum     WHERE       prmchg.status NOT IN (5,6)       AND sbcgln.cstcde<>0   ) AS CostCodeList WHERE   Job=?

Note the red-highlighted text has been removed in the corrected version. The green-highlighted text at the top and bottom of the corrected version effectively create a "wrapper" query around the original UNION query; using the original UNION query as if it were a table in the FROM clause. The green-highlighted text within the UNION query simply adds the Job number as an output column so that it can be used in the WHERE clause of the outer (i.e., wrapper) query. In the corrected version, the query has only one parameter (for the job number) as opposed to the two parameters in the original version of the query.


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