Re: Need advice on NewEra

Dmajor7 (dmajor7@aol.com) wrote: : I have a problem with Informix NewEra. I have a table T1 which has an (see the end of the article for complete posting) I have done similar tests, but have not encountered the alleged problem. Your solution seems overly complicated for something that is straightforward: declare C1 cursor for select * from T1 where A=X order by A,B,C # optional order byforeach C1 into T1_record ... do stuff ... select * from other_table where ... if sqlca.sqlcode != notfound then ... specific stuff ... end if end foreach I wonder if you could post the code that gave you problems and include some sample data. Catalin. P.S. I'd stay away from GOTO statements (in any language) _____ _____ _____ _____ _ _ _____ Catalin Badea _____/ _____/ _____/ _____/ _/_ _/ _____/ Senior Technical Architect _/ _/___ _/ _/____ _/_/ _/____ Tecsys Inc. _/ ____/ _/ _____/ _/ _____/ _/ _/____ _/___ ____/ _/ ____/ cat@tecsys.com _/ _____/ _____/ _____/ _/ _____/ include system "disclaim.4gh" -- : 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 that : table 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 : -------------------------------------------------------------------------- : ------------------------------