Hi all,

With the following Query I got some unusual results. I prepare it once and
try to use it more than once changing the OrderingItemNo. When I do this,
the dataset comes back empty. If I immediately try it again, it works OK --
until I change the OrderingItemNo again. I put IB Monitor on it and found
that when you change the OrderingItemNo, the Open command will cause a new
prepare, but that when that happened, one variable 'ComplaintType' was being
replaced as NULL. If I change the Parameter name to 'CT' instead of
'ComplaintType', then everything works as expected. I did this as a
workaround, but wondered if we need to take a closer look at the parameter
replacement code or if I am missing something (high probability).

Marv


SELECT COMPLAINT.OPENDATE
, COMPLAINT.RESOLUTIONDATE
, COMPLAINT.RESOLVEDBY
, COMPLAINT.CSCLOSED
, (COMPLAINT.FirstNAME ||' '||COMPLAINT.LastNAME) as CustomerName
, COMPLAINT.ADDRESSNO
, STREETNAMES.SNAME
, COMPLAINT.PHONE
, COMPLAINT.COMPLAINTTYPE
, COMPLAINTTYPE.CTYPID
, COMPLAINTTYPE.NAME
, EMPLOYEE.EID
, EMPLOYEE.LASTNAME
, EMPLOYEE.FIRSTNAME
, EMPLOYEE.MI
, STREETNAMES.SNAME
, STREETNAMES.SNAMESORT
, CPID
FROM COMPLAINT
Left Outer JOIN COMPLAINTTYPE on (ComplaintType.ctypid =
complaint.complainttype)
Left Outer JOIN EMPLOYEE on Employee.eid = Complaint.resolvedby
Left Outer JOIN STREETNAMES on STREETNAMES.SID = Complaint.sid
Where complaint.water = :water
and Complaint.ComplaintType = :COMPLAINTTYPE <<--- Problem Here --------
and complaint.ResolutionDate Between :StartDate and :EndDate