Re: select with default

On Thu, 16 Apr 1998 11:09:26 -0400, Igor Sereda <sereda_at_spb.runnet.ru> wrote:

>Hello,
>
>i wonder if there is a way to make a pl/sql code like this:
>
> begin
> select FIELD into VAR from TABLE where CONDITION;
> exception
> when NO_DATA_FOUND then VAR := DEFAULT;
> end;
>
>without exception handling, just in one line, straight and simple.
>I found that such sentenses arise very often and i suppose there
>should be a way to simplify this.
>
>Thank you,
>Igor

Hi Igor,

try the following code:

SELECT nvl(max(FIELD), <your_default_goes_here>)   INTO var
  FROM table
 WHERE condition;

If a record exists, you will get the field value; if none exists, the aggregate function max will return a NULL which the nvl takes care of. But OTOH: if more than one record is there, you will still get only one value back, whereas in your previous code you would have been able to detect this by an exception handler for too_many_rows.

HTH
Peter

--
Peter Schneider
peter.schneider_at_okay.net Received on Thu Apr 16 1998 - 14:34:25 CDT