A subquery has returned not exactly one row

[Chris West-MedAS]

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....@bbc.co.uk

[Aaron Brooks]

Chris West-MedAS wrote:

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: abr...@pcservice.com Home: abr...@airmail.net