RE: cursor

a simplified version...
the second cursor was looping thru the 2nd table needlessly...
just loop thru the first table once, and update the column you need as you
go...
I didn't understand how you wanted to update, overlay the masterfield or
append to it...

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

        v_f1    VARCHAR2(12);
        FLAG    NUMBER(1) := 1;
BEGIN
    FOR v1 IN c1 
    LOOP
		dbms_output.put_line(v1.dot_number || ' ' ||
v1.classification_id);
		update censusomc 
			set master_field = v1.classification_id
--			set master_field = censusomc.dot_number ||
v1.classification_id
				where censusomc.dot_number = v1.dot_number;
END LOOP;
--DBMS_OUTPUT.PUT_LINE('value is: '||v_f1);
end;
/

-----Original Message-----
From: Natividad Castro [mailto:[email protected]]
Sent: Wednesday, December 04, 2002 7:35 AM
To: Oracle
Subject: [oracle] 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.unsub%%.

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.




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