Re: Select into difficulties

From: Bryan D. McClure <bmcclu19_at_mail.idt.net>
Date: 1998/01/15
Message-ID: <69ktin$f6l@nnrp3.farm.idt.net>#1/1

Laura Bellini wrote in message <01bd2109$ac04a610$863612ac_at_pw48>...
>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

Or you could do it this way.

CURSOR match_prod_id_curs(prod_id_in IN <base_prod_id type goes here>) IS
SELECT match_prod_id
FROM product_up-cross_sell

WHERE base_prod_id = prod_id_in
AND match_type = 'U';

new_prod_id_out product_up-cross_sell.base_prod_id%TYPE;

BEGIN    OPEN match_prod_id_curs(pass_in_prod_id);    FETCH match_prod_id_curs INTO new_prod_id;

      IF match_prod_id_curs%NOTFOUND THEN
         new_prod_id_out := pass_in_prod_id;
     ELSE
        NULL;
     END IF;

   CLOSE match_prod_id_curs;

   ...
   blah, blah, blah
   ...

END;

--
Bryan D. McClure
PCS Technologies, Inc.
bmcclure_at_acm.org

Received on Thu Jan 15 1998 - 00:00:00 CST