Hi All, i am new to DB2 but after some RnD i have written this SP to accomplish my need. I am making some update in statement at some condition , in debug mode it comes to update only at those cndtns but when i see at end the update happens at all records for that field.

Please let m eknow if i am doing something wrong?

Thanks

Prashant.

Here is the SP

CREATE PROCEDURE PRAGS.TESTOT ( )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare variable
DECLARE ENDTABLE INT DEFAULT 0;
DECLARE item_objid INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE itm_id VARCHAR(10);
DECLARE ec_mc_id VARCHAR(10);
DECLARE s_itemi VARCHAR(10);
DECLARE s_idcng VARCHAR(10);

-- Declare cursor
DECLARE cursor1 CURSOR FOR
SELECT OBJECT_DATA.ITEM_OBJID, S_ITEMI, S_IDCNG FROM PRAGS.OBJECT_DATA AS OBJECT_DATA INNER JOIN PRAGS.PART_LIST AS PART_LIST
ON OBJECT_DATA.ITEM_OBJID = PART_LIST.ITEM_OBJID WHERE OBJECT_DATA.S_DC='RD';

-- Declare handler
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET ENDTABLE = 1;

OPEN cursor1;

SET ENDTABLE = 0;
FETCH FROM cursor1 INTO item_objid, s_itemi, s_idcng;
WHILE ENDTABLE = 0 DO
SELECT COUNT(*) INTO num_rows FROM PRAGS.AVNTMITEM AVNTMITEM JOIN PRAGS.AVENTACI AVENTACI ON AVNTMITEM.OID=AVENTACI.OID_MITEM JOIN PRAGS.AVNTEC AVNTEC ON AVNTEC.OID=AVENTACI.OID_EC WHERE ITM_ID = s_itemi AND EC_MC_ID = s_idcng;

IF num_rows >= 1 THEN
UPDATE PRAGS.OBJECT_DATA SET Q_PMSYNC = 'Y' WHERE ITEM_OBJID = item_objid AND S_IDCNG = s_idcng;
END IF;

FETCH FROM cursor1 INTO item_objid, s_itemi, s_idcng;
END WHILE;
CLOSE cursor1;
END P1

I would take a hard look at the data. I find it hard to believe that DB2 is just updating every row for the fun of it. I think it is doing exactly what you told it to do.

Andy