Re: Stored procs: detecting when SELECT returns no records

andrew@infi.net wrote: : I am an SQL novice trying to teach myself Informix on AIX using : nothing more than the Informix manuals. While the books seem : pretty good, stored procedures seem to be only BARELY : documented. Much is left to the imagination and trial/error. : What is the proper way, in a stored procedure, to detect when a : single-row SELECT doesn't find a row? The only mechanism : I saw in the manuals was the ON EXCEPTION statement. However, : when I tried it, I observed no exception. What happened instead Keep in mind, a NOTFOUND condition is not an error and therefore cannot be trapped by an ON EXCEPTION statement. : was that the variable I was selecting into was set to NULL. Is : that the only indication that a SELECT didn't find a record? : Seems ambiguous unless the column in question has a not-null : constraint. True, but if you select a key column, whose value should never be null, you can then test it for NULL. : Basically, here's the *pseudo* code for what I'm trying to do in : this particular procedure. If someone would be kind enough to : suggest the proper way to accomplish this in an Informix stored : procedure, I'll be very grateful: : SELECT quantity FROM history : WHERE history.sku = sku AND history.date = date; : IF no record found : INSERT new record into history table : ELSE : UPDATE quantity in existing record in history table I like the INSERT suggestion already posted here. If you put the INSERT statement into its own statement block you can trap the error with an ON EXCEPTION statement at the top of the statement block. If you don't like that method, another, more generic method for detecting a NOTFOUND condition is to put the SELECT statement into a FOREACH block. Set some integer procedure variable to +100 (I like +100 because it's the NOTFOUND SQL code) prior to entering the FOREACH block. Immediately after the SELECT, but still inside the FOREACH block, reset the procedure variable to 0. Then, after the END FOREACH statement test the variable for +100. If it hasn't been reset to 0, there were no rows found by the SELECT. ___ ___ Principal Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 dberg@informix.com Standard disclaimers apply. Eventually all things merge into one...and a river runs through it. -N.Maclean