Need advice on NewEra

I have a problem with Informix NewEra. I have a table T1 which has an index whose keys are A, B, and C. I want to process all the rows which have A=X. This should be a simple matter of setting up a cursor for select * from T1 where A=X and doing a foreach loop on that cursor. Unfortunately, I need to access another table inside this loop and on thattable I may or may not get a not found condition. If I use a foreach loop, not found on the other table will close my cursor in the outer loop. The only way around this I can think of is to code declare C1 cursor for select * from T1 where A=X and ((B = prev_B and C > prev_C) or B > prev_B) let prev_B = null let prev_C = null while true open C1 whenever not found go to thats_all fetch C1 into T1_record close C1 let prev_B = T1_record.B let prev_C = T1_record.C . . . whenever not found go to not_this select * from other_table where ... . . .label not_this: end while label thats_all There are two problems with this. 1. Opening and closing the cursor each time involves significant overhead. and 2. This is the BIG ONE. Because of the nature of the select statement involved, SQL is not able to figure out that he can use the index to get right to the record required and so uses the index to get to the first record with A=X and then reads sequentially from that point to get to the required record. If I could use a foreach loop it would solve the problem, BUT NO! A not found on ANY structure causes ALL cursors to be closed. Do any other NewEra or 4GL users find this as absurd as I do. Does anyone know a way to check for the existence of a record before doing a select so that I can avoid doing to select if I know I will get a not found? If I select count(*) from <table> where <condition> and it returns 0, is that the same as a not found? I need to tell you, I've only been working with Informix for a week and so I'm not very knowledgeable yet on the intricacies of NewEra. My company is evaluating both Informix and Oracle for a big database project we're undertaking and I'm the guy who's going to make the final recommendation. I really like the tools Informix has better than Oracle's tools but this one problem may force me to choose Oracle. Any suggestions anyone has will be appreciated. -------------------------------------------------------------------------- ------------------------------ Ken Denny, Insight Industries Inc (I cubed) All opinions expressed are ... (the usual bit) No cute graphic stuff because some people (myself included) use fonts other than Courier which makes the cute graphic stuff not so cute -------------------------------------------------------------------------- ------------------------------