cursor

hi to all,
I don't know if this the right place to post this question. If it's not, my
aplogies.

Below is the structure of my tables
TABLE CARRIER_OP_CLASS
DOT_NUMBER 		CLASSIFICATION_ID
777				1
777				2
888				3
888				4

TABLE CENSUSOMC
DOT_NUMBER 		MASTER_FIELD
777				12
888				34

As you can see what I'm trying to do is get all classification_id that
belong to the dot_number field on CARRIER_OP_CLASS and append them to the
MASTER_FIELD on CENSUSOMC table where dot_number match.

For some reason my cursors are not working right, what is doing now is will
update the first record with 12, but when it goes to the next record will
add 1234.

It looks like this
dot_number 777 master_field 12
dot_number 888 master_field 1234

Any idea?
Thanks in advance
Nato


SET SERVEROUTPUT ON
DECLARE
        cursor c1 is
        SELECT DOT_NUMBER, CLASSIFICATION_ID
        FROM CARRIER_OP_CLASS;
        v1    VARCHAR2(2);

        cursor c2 is
        SELECT DOT_NUMBER, MASTER_FIELD FROM CENSUSOMC;
        v_f1    VARCHAR2(12);
        FLAG    NUMBER(1) := 1;
BEGIN
    FOR v1 IN c1 LOOP
        --FLAG := 1;    -- just added
        FOR V2 IN c2 LOOP

            IF v1.DOT_NUMBER = V2.DOT_NUMBER then
               IF FLAG = 1 THEN
                  v_f1 := v1.classification_id;
        DBMS_OUTPUT.PUT_LINE('value now is '||v_f1);
              FLAG := 2;
              ELSE
                 v_f1 := v_f1 || v1.classification_id;
                update censusomc set master_field = v_f1 where
censusomc.dot_number =
v1.dot_number;

               end if;
            end if;
         END LOOP;
    END LOOP;
DBMS_OUTPUT.PUT_LINE('value is: '||v_f1);
end;
/


---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].