Archives of the TeradataForum

Message Posted: Wed, 17 Aug 2005 @ 21:12:57 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi, I am trying to create procedure and have problem when my select statement doesn't return any records. How to handle it?

     Create procedure zs_upd_instance()
     begin
     declare  v_PURCHASE_DATE_ID                        decimal(10,0)     ;
     declare  i_PURCHASE_DATE_ID             decimal(10,0)     ;
     FOR C AS C1
     CURSOR FOR
                             SELECT
                                         * FROM ZS_INSTANCE
                             order by dw_partner_prod_inst_id,
     PARTNER_PRODUCT_INSTANCE_ID
                             DO
     SELECT

     coalesce(PURCHASE_DATE_ID, null, 0) PURCHASE_DATE_ID
                                         INTO
                                                     :v_PURCHASE_DATE_ID
                                         FROM ZS_INSTANCE_TR
                                         WHERE DW_PARTNER_PROD_INST_ID =
     :c.DW_PARTNER_PROD_INST_ID
                                         AND CURRENT_FLAG = 'Y';

     --This is from ORACLE handler
     --EXCEPTION
     --WHEN NO_DATA_FOUND THEN
     --INSERT INTO ZS_INSTANCE_TR VALUES
     --(:c.PARTNER_PRODUCT_INSTANCE_ID, c.PURCHASE_DATE_ID, c.CANCEL_DATE_ID,
     c.FULFILLED_DATE_ID,
     --c.ASSOCIATED_DOMAIN_NAME,
     --c.DW_PARTNER_PROD_INST_ID,
     --'Y',
     --null,
     --DATE);
     END FOR;
     IF         c.PURCHASE_DATE_ID = 0 THEN
     set i_PURCHASE_DATE_ID = v_PURCHASE_DATE_ID;
     else
     set i_PURCHASE_DATE_ID = c.PURCHASE_DATE_ID;
     END IF;
     UPDATE ZS_INSTANCE_TR SET CURRENT_FLAG = 'N', OBSOLETE_DATE = DATE WHERE
     DW_PARTNER_PROD_INST_ID = :c.DW_PARTNER_PROD_INST_ID
                                         AND CURRENT_FLAG = 'Y';
     END FOR;
     END;

My problem is when second select statement returns no record. In that case I want to insert record into target table (ZS_INSTANCE_TR).

Any ideas would be appreciated.

Thanks,

Zdravko Sonje

	<Prev	Next>		<<First	<Prev	Next>	Last>>