Re: [GENERAL] Catch multiple records when doing Select Into
Hugh, that's a long way to do that. Thanks for the help. SWK Ketema wrote: > It would be easier if we could see the context in which you are doing > the select into. However I think this may help. Try putting the > select into in a loop: > > declare > _result record; > _rows integer; > begin > _rows := 0; > for _result in select statement here loop >in here put logic to raise your exception if you get more > than one result > _rows := _rows + 1; > if _rows > 1 then raise exception 'Hey too many rows'; > return next _result; > end loop; > end; > > On Oct 31, 4:15 am, "SunWuKung" <[EMAIL PROTECTED]> wrote: > > I have a SELECT INTO varname columname FROM ... statement and I would > > like to raise an exception when that returns more than one record. > > GET DIAGNOSTICS rowcount = ROW_COUNT seems to return always one for > > this. Currently I find no other way to do that than to run this > > statement twice - first to see how many records it returns, which seems > > very inefficient. > > > > I can see that this will be handled for 8.2, but for 8.1 what is the > > best practice to do this? > > > > Thanks. > > SWK ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Catch multiple records when doing Select Into
It would be easier if we could see the context in which you are doing the select into. However I think this may help. Try putting the select into in a loop: declare _result record; _rows integer; begin _rows := 0; for _result in select statement here loop in here put logic to raise your exception if you get more than one result _rows := _rows + 1; if _rows > 1 then raise exception 'Hey too many rows'; return next _result; end loop; end; On Oct 31, 4:15 am, "SunWuKung" <[EMAIL PROTECTED]> wrote: > I have a SELECT INTO varname columname FROM ... statement and I would > like to raise an exception when that returns more than one record. > GET DIAGNOSTICS rowcount = ROW_COUNT seems to return always one for > this. Currently I find no other way to do that than to run this > statement twice - first to see how many records it returns, which seems > very inefficient. > > I can see that this will be handled for 8.2, but for 8.1 what is the > best practice to do this? > > Thanks. > SWK ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Catch multiple records when doing Select Into
I have a SELECT INTO varname columname FROM ... statement and I would like to raise an exception when that returns more than one record. GET DIAGNOSTICS rowcount = ROW_COUNT seems to return always one for this. Currently I find no other way to do that than to run this statement twice - first to see how many records it returns, which seems very inefficient. I can see that this will be handled for 8.2, but for 8.1 what is the best practice to do this? Thanks. SWK ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
