> I have a relatively simple query that periodically fails to find the
> data it is looking for.
>
> SELECT QUANTITY, M.COMMENT, P.DESCRIPTION1, M.PROGRESS
> FROM MRP M, PARTS P
> WHERE P.STOCK_CODE = '123456'
> AND M.STOCK_CODE=P.STOCK_CODE
> AND ((M.PROGRESS > 0) AND (M.PROGRESS < 5))
> ORDER BY MDATE, M.CONTRACT ASCENDING
>
> This will periodically return EOF when a valid record exists - both in
> an application and via IBExpert
>
> The problem is with (M.PROGRESS > 0). PROGRESS is an integer field
> and there is a single valid record with a value of 1.
>
> Changing the condition to (M.PROGRESS >= 1) correctly returns the
> record.
>
> If I backing up the database (Server Version: LI-V2.0.0.12748 Firebird
> 2.0) and restoring it my desktop (Server Version: WI-V2.0.0.12748
> Firebird 2.0) it returns the correct record.
>
> On the Linux server using IBExpert to change the value to 2, and then
> changing it back to 1 corrects the problem. Have spotted this on 2
> records in the last week.
>
> Same plan for both.

have you tried join syntax?
(assume QUANTITY is from M, and MDATE is from M )

> SELECT M.QUANTITY, M.COMMENT, P.DESCRIPTION1, M.PROGRESS
> FROM MRP M join PARTS P on M.STOCK_CODE=P.STOCK_CODE

where P.STOCK_CODE = '123456'

> AND ((M.PROGRESS > 0) AND (M.PROGRESS < 5))
> ORDER BY M.MDATE, M.CONTRACT ASCENDING

just wondering
Alan