Needs Help, Error 243 / 111 by creating tmp Table

Hello, I have following problem, sometimes the following Sql Command fails: SELECT distinct b.pabx_id, b.dev_cl_gr,b.dev_class,b., a.switch FROM OUTER filtconftable a, alarm b WHERE a.pabxid=b.pabx_id AND a.group=b.dev_cl_gr AND a.subid=b.dev_class AND a.priority = b.priority AND b.pabx_id=1 INTO temp test_temp I got the following error message: Fri Nov 14 03:02:00 2003: Fxs023 SELECT_STATEMENT_FAILED Filename: unxsadba.ec, Line: 865 Description: A SELECT statement failed. Refer to your INFORMIX error message manual for further information > Table: test_tmp - SQL-Error -243 ISAM-Error -111 Which means the database could not found the requested record. I dont understand in which circumstances this error can occur. I test it with both tables empty , with one table empty , with deleting the records of one table while creating the tmp table. And could not reproduce the error. So i wrote here :-) I hope some of you can help me and can explain when and why this error can occur . Thx for all help Thorsten

243 is a locking error so at the time of running somebody was holding a lock on one of the two tables. Thorsten Geelhaar wrote: > > Hello, > I have following problem, sometimes the following Sql Command fails: > > SELECT distinct b.pabx_id, b.dev_cl_gr,b.dev_class,b., a.switch > FROM OUTER filtconftable a, alarm b > WHERE a.pabxid=b.pabx_id > AND a.group=b.dev_cl_gr > AND a.subid=b.dev_class > AND a.priority = b.priority AND b.pabx_id=1 > INTO temp test_temp> > I got the following error message: > > Fri Nov 14 03:02:00 2003: Fxs023 SELECT_STATEMENT_FAILED > Filename: unxsadba.ec, Line: 865 > Description: A SELECT statement failed. > Refer to your INFORMIX error message manual for further information > > Table: test_tmp - SQL-Error -243 ISAM-Error -111 > > Which means the database could not found the requested record. > I dont understand in which circumstances this error can occur. > I test it with both tables empty , with one table empty , with deleting the records of one table > while creating the tmp table. And could not reproduce the error. > > So i wrote here :-) > > I hope some of you can help me and can explain when and why this error can occur . > > Thx for all help > > Thorsten -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

Paul Watson wrote: > 243 is a locking error so at the time of running somebody > was holding a lock on one of the two tables. > Thanks Paul ,but on my system finderr 243 says : -243 Could not position within a table table-name. The database server cannot set the file position to a particular row within the file that represents a table. Check the accompanying ISAM error code for more information. A hardware error might have occurred, or the file might have been corrupted (truncated). Unless the ISAM error code or an operating-system message points to another cause, run the bcheck or secheck utility to verify file integrity. and finderr 111: -111 ISAM error: no record found. The ISAM processor cannot locate the requested record. For C-ISAM programs, no record was found with the requested index value or record number, depending on the retrieval mode in use. Make sure that the correct index is in use. For SQL products, see the SQL error message or return code. Probably no row was found for this query. Maybe i had to say that i use a Dynamic 2000 Server 9.21 . Sorry if this leads to misunderstoods. > Thorsten Geelhaar wrote: > >>Hello, >>I have following problem, sometimes the following Sql Command fails: >> >>SELECT distinct b.pabx_id, b.dev_cl_gr,b.dev_class,b., a.switch >> FROM OUTER filtconftable a, alarm b >> WHERE a.pabxid=b.pabx_id >> AND a.group=b.dev_cl_gr >> AND a.subid=b.dev_class >> AND a.priority = b.priority AND b.pabx_id=1 >> INTO temp test_temp>> >>I got the following error message: >> >>Fri Nov 14 03:02:00 2003: Fxs023 SELECT_STATEMENT_FAILED >>Filename: unxsadba.ec, Line: 865 >>Description: A SELECT statement failed. >>Refer to your INFORMIX error message manual for further information >> > Table: test_tmp - SQL-Error -243 ISAM-Error -111 >> >>Which means the database could not found the requested record. >>I dont understand in which circumstances this error can occur. >>I test it with both tables empty , with one table empty , with deleting the records of one table >>while creating the tmp table. And could not reproduce the error. >> >>So i wrote here :-) >> >>I hope some of you can help me and can explain when and why this error can occur . >> >>Thx for all help >> >>Thorsten > > -- Thorsten Geelhaar Software-Entwickler Netzwerk und System Management Business Unit Information ______________________________________________ Materna GmbH Information & Communications Vosskuhle 37 * 44141 Dortmund * Germany phone: 0049 - 231 - 5599 - 106 fax : 0049 - 231 - 5599 - 272

On Tue, 16 Dec 2003 15:37:35 +0100, Thorsten Geelhaar <thorsten.geelhaar@materna.de> wrote: > > >Paul Watson wrote: >> 243 is a locking error so at the time of running somebody >> was holding a lock on one of the two tables. >> > >Thanks Paul ,but on my system finderr 243 says : > >-243 Could not position within a table table-name. > >The database server cannot set the file position to a particular row >within the file that represents a table. Check the accompanying ISAM >error code for more information. A hardware error might have occurred, >or the file might have been corrupted (truncated). Unless the ISAM >error code or an operating-system message points to another cause, run >the bcheck or secheck utility to verify file integrity. > >and finderr 111: > >-111 ISAM error: no record found. > >The ISAM processor cannot locate the requested record. For C-ISAM >programs, no record was found with the requested index value or record >number, depending on the retrieval mode in use. Make sure that the >correct index is in use. For SQL products, see the SQL error message or >return code. Probably no row was found for this query. > > >Maybe i had to say that i use a Dynamic 2000 Server 9.21 . >Sorry if this leads to misunderstoods. > Somewhere there's a lock on one of the tables in the select. Might need to use . . . set isolation to dirty read...in order to ignore all locks.