Re: Select into difficulties

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: 1998/01/15
Message-ID: <34BD696F.5771@bhp.com.au>#1/1

Laura Bellini wrote:
>
> Hi.
>
> I am trying to populate a variable via a simple SELECT INTO statement.
> But, if the SELECT INTO returns no rows, I want to populate the variable
> with something else and continue the procedure. I DO NOT want to
> exception-out of the proc. Here is the statement w/in my proc:
>
> SELECT MATCH_PROD_ID
> INTO NEW_PROD_ID_OUT
> FROM PRODUCT_UP-CROSS_SELL
> WHERE PRODUCT_UP-CROSS_SELL.BASE_PROD_ID = PROD_ID_IN
> AND PRODUCT_UP_CROSS_SELL.MATCH_TYPE = 'U'
>
> PROD_ID_IN is an incoming parameter.
> If the above returns no rows, I just want to populate NEW_PROD_ID_OUT with
> PROD_ID_IN.
>
> Very simple, I think, but I'm unable to determine a way of doing it.
>
> thanks in advance.
>
> Laura Bellini
> laura_bellini_at_compaq.com

You can use nested blocks to remain in your proc:

procedure my_proc is
begin
  begin
    SELECT MATCH_PROD_ID

    INTO            NEW_PROD_ID_OUT
    FROM            PRODUCT_UP-CROSS_SELL
    WHERE   PRODUCT_UP-CROSS_SELL.BASE_PROD_ID = PROD_ID_IN
    AND     PRODUCT_UP_CROSS_SELL.MATCH_TYPE = 'U'
  exception when no_data_found then
     NEW_PROD_ID_OUT := PROD_ID_IN;

  end;

• rest of code continues here... end; --

  ---

  Connor McDonald BHP Information Technology Perth, Western Australia "These views mine not BHP..etc etc"

"The only difference between me and a madman is that I am not mad." Received on Thu Jan 15 1998 - 00:00:00 CST