Just for curiosity, what happens if you put all your table/field names / parameters to UPPERCASE? It could be that in some part IBO fails in doing Complaint.ComplaintType = :COMPLAINTTYPE...
Just a supposition...
Thanks
Marco Menardi

--- In IBObjects@y..., "Marv Cook" <mjcook@t...> wrote:
> 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