|Subject||RE: [firebird-support] SELECT FAILURE|
> I have a relatively simple query that periodically fails to find thehave you tried join syntax?
> 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
> If I backing up the database (Server Version: LI-V18.104.22.16848 Firebird
> 2.0) and restoring it my desktop (Server Version: WI-V22.214.171.12448
> 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.
(assume QUANTITY is from M, and MDATE is from M )
> SELECT M.QUANTITY, M.COMMENT, P.DESCRIPTION1, M.PROGRESSwhere P.STOCK_CODE = '123456'
> FROM MRP M join PARTS P on M.STOCK_CODE=P.STOCK_CODE
> AND ((M.PROGRESS > 0) AND (M.PROGRESS < 5))just wondering
> ORDER BY M.MDATE, M.CONTRACT ASCENDING