Re: 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" <Balazs(dot)Kl(dot)(dot)(dot)(at)t-online(dot)hu> 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