Re: How to do a "select any" using Informix SQL?
In article <32197s$j2b@emory.mathcs.emory.edu>,
Almamater@aol.com writes:
:Does anybody know a simple and efficient way of selecting any record in a
:non-singleton select statement without having to resort to 4GLxs open,
fetch
:and close?
The only way I found to do this is, (and it's not that effecient, I might
add) is to
convert the initial SQL statement into a subquery that finds max of rowid
(or
some other unique key).
Assume the original SQL statement is:
select *
into t1_rec.*
from t1
where condition1
However, since the query is non-singleton (>1 rows meeting condition1 in
t1)
you will get the infamous"subquery returned more that one row, etc, etc".
You could rewrite the query as:
select *
into t1_rec.*
from t1
where rowid = (select max (rowid)
from t1
where condition1)
Outside of this workaround I don't know of another way to accomplish this.
Some years back I tried to perform the original query enclosed within
WHENEVER ERROR CONTINUE and WHENEVER ERROR STOP but to
my recollection, the target record of the INTO was not filled. This may
be changed.
Hope this helps,
Mike Teachworth
CodeGen Software
(800) 598-8649 (voice)
(504) 456-1260 (fax)
mike@codegen.com