Re: How to do a "select any" using Informix SQL?

MikeT300 (miket300@aol.com) wrote: : 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 Sorry to leave the whole post intact to reply but I don't want to have this taken out of context. Why would you not want to use the open fetch close method? Mikes code works and as he said, slowly. What task are you trying to perform? If you're trying to see if rows exists, you could select count(*) intoo a variable where ... This would be just as slow but would yeild more info. If you'll post _what_ you want to do, we can better tell you how. -- gburnore@garys.arasmith.com ------------------------------------------------------------------------------- How you look depends on where you go. ------------------------------------------------------------------------------- | '۳''''''۳''''''''''''' Gary L. Burnore | '۳''''''۳''''''''''''' Director, Programming & Development | '۳''''''۳''''''''''''' Myers Information Systems, Inc. | '۳''''''۳''''''''''''' | Official Proof of Purchase ===============================================================================