Icon View Incident Report

Serious Serious
Reported By: Michael Collins
Reported On: 6/27/2001
For: Version 2.10 Build 1
# 811 Partial Match with NOT LIKE Operator in SQL Un-Optimized WHERE Expression Causes Incorrect Results

I am using ReportBuilder and it is generating this SQL which looks fine to me but is not doing what I need (not show any record that has 'CDS' in the PCompID field.

SELECT CompDef.PCompID PCompID_2,
       WrkDone.BillHrs, WrkDone.BreakHrs,
       WrkDone.Catagory, WrkDone.ClientID,
       WrkDone.ContractID, WrkDone.EndDate,
       WrkDone.EndTime, WrkDone.Offsite,
       WrkDone.OffSiteHrs, WrkDone.OnSite,
       WrkDone.OnSiteHrs, WrkDone.PCompID,
       WrkDone.ProjectID, WrkDone.StartDate,
       WrkDone.StartTime, WrkDone.TravelHrs,
       WrkDone.WorkDescOffSite,
       WrkDone.WorkDescOnSite,
       WrkDone.WorkEntered, WrkDone.WorkerID,
       WrkDone.WorkTypeID
FROM "CompDef.dat" CompDef
      INNER JOIN "WrkDone.dat" WrkDone ON
     (WrkDone.PCompID = CompDef.PCompID)
WHERE ( WrkDone.PCompID NOT LIKE 'CDS%' )
ORDER BY CompDef.PCompID, WrkDone.PCompID,
         WrkDone.StartDate



Comments Comments and Workarounds
The workaround was to add a case-sensitive index on th PCompID field, which would fix the problem.


Resolution Resolution
Fixed Problem on 6/28/2001 in version 2.11 build 1
Image