A subquery has returned not exactly one row

Maybe someone can shed some light on this ... I have a 4GL program that VERY occasionally fails with -284 (see subject) when performing a simple SELECT * INTO l_rec.* FROM table_a WHERE col_a = var_a. col_a is defined as CHAR(12) There is a unique index on col_a. When I attempt to query the duplicates within isql, obviously, there aren't any. The entry had previously been inserted within the same program, a serial value on a different column is retrieved (using SQLCA.SQLERRD[2]) and used to build the value for col_a, followed by an UPDATE - The subsequent SELECT then fails as above. The reasoning behind some of the program logic is a little unclear, but I can't find any technical reason for this error, nor recreate it at will - any ideas? Versions: HP/UX 10.20 INFORMIX-OnLine Version 7.24.UC7 INFORMIX-4GL Version 7.20.UD6 (RDS) Many Thanks Chris West Chris.West.01@bbc.co.uk

Chris West-MedAS wrote: > Maybe someone can shed some light on this ... > > I have a 4GL program that VERY occasionally fails with -284 (see subject) > when performing a simple SELECT * INTO l_rec.* FROM table_a WHERE col_a = > var_a. col_a is defined as CHAR(12) > > There is a unique index on col_a. When I attempt to query the duplicates > within isql, obviously, there aren't any. The entry had previously been > inserted within the same program, a serial value on a different column is > retrieved (using SQLCA.SQLERRD[2]) and used to build the value for col_a, > followed by an UPDATE - The subsequent SELECT then fails as above. > > The reasoning behind some of the program logic is a little unclear, but I > can't find any technical reason for this error, nor recreate it at will - > any ideas? > > Versions: > > HP/UX 10.20 > INFORMIX-OnLine Version 7.24.UC7 > INFORMIX-4GL Version 7.20.UD6 (RDS) > > Many Thanks > Chris West > Chris.West.01@bbc.co.uk Chris, This error only happens when the query finds more than one match and tries to load it into the variables. A quick fix, is to create a cursor and drop out (exit foreach) after retrieving the first result. (Hopefully the first one is the one you want). This doesn't find why you're getting duplicates on your unique key, but it hides the bug and makes your program work. -- --------------------------------------------------------------- Aaron A. Brooks Senior Manager, Information Logistics PC Service Source Work: abrooks@pcservice.com Home: abrooks@airmail.net