Re: SPL: How to react to NOTFOUND

Richard Spitz wrote: > > Hi, > > in a Stored Procedure, I have an implicit cursor with FOREACH. However, > it is possible that the SELECT statement returns no rows, and in that > case, > I want to have a completely different action. > > I tried catching the NOTFOUND with ON EXCEPTION IN (100), but it seems > that NOTFOUND doesn't raise an exception at all. Is that correct? How > can > I find out in my procedure whether the SELECT returned a NOTFOUND? > > Regards, Richard > -- > +--------------------------+------------------------------------------+ > | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | > | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3413 | > | Klinikum Grosshadern | FAX : +49-89-7095-8886 | > | 81366 Munich, Germany | | > +--------------------------+------------------------------------------+ Hi Richard, you can try to use a variable to flag if something was found or not, i.e. LET l_cnt = 0; FOREACH SELECT ... LET l_cnt = l_cnt + 1; END FOREACH IF l_cnt = 0 THEN RAISE EXCEPTION -9999,0; END IF As a side-effect you have the number of records found accessible -) HTH Tolis +---------------------------------------------------------------------+ | V+K Relational Solutions email: tvarnas@compulink.gr | | Deligiorgi 26 tvarnas@orbit.de | | 546 42 Thessaloniki Voice: (30) 31 820270 | | Greece Fax: (30) 31 865463 | +---------------------------------------------------------------------+