Icon View Incident Report

Serious Serious
Reported By: Vinnie Murdico
Reported On: 3/27/2002
For: Version 3.08 Build 1
# 1071 Using Constants with Same Names as Fields in IN Expressions Causes Type Mismatch Error

I am evaluating DBISAM for our product, which currently uses Paradox and the BDE. I have an SQL statement that used to work under the BDE, but is now giving an error. The error it generates is:

DBISAM Engine Error # 11949 SQL error - Type mismatch between IN operator and expression 'Defect' in WHERE or JOIN clause

Ok, here is the problem: In the "IN" clause where it says "...and Nature in ('Research','Defect','Enhancement')...", we have 'Defect' as one of the choices to filter the Nature field on. When 'Defect' is one of the choices, the program bombs.

If I remove 'Defect" from the choices in the IN clause (with no other changes), it works fine.

I'm wondering if DBISAM is confusing the *choice* 'Defect' from the IN clause's list of valid filter selections with the *table name* 'Defect' which is one of the tables we use in the Select clause.

Select Defect.Product, Defect.Release, Defect.Program, Defect.Defect, 

[...snipped other selected fields...]
[... snipped multiple join clauses...]
WHERE (  (Product = 'DynaCalc') and (Release = '1.0') and (Program 
= 'dynacalc.exe') and Program.Status  in ('Pending','Approved') and 
in ('Pending Research','Confirmed','Pending Development',
Development','Pending QA','In QA','Pending Rework') and Nature  
('Research','Defect','Enhancement') and Severity  in ('Minor',
'Moderate') and 
(Priority >= 1) and (Priority <= 999))  Order By Defect'

Resolution Resolution
Fixed Problem on 3/30/2002 in version 3.09 build 1