RE: oracle digest: December 04, 2002
"Natividad Castro" <[email protected]>
| Newsgroups | gmane.comp.db.oracle.devel |
|---|---|
| Message-ID | <LYRIS-1796914-793226-2002.12.06-16.25.42--gcdod-oracle#[email protected]> |
Thank you for all your help guys Nato -----Original Message----- From: Oracle digest [mailto:[email protected]] Sent: Wednesday, December 04, 2002 7:04 PM To: oracle digest recipients Subject: oracle digest: December 04, 2002 ----------------------------------------------- When replying to the digest, please quote only relevant material, and edit the subject line to reflect the message you are replying to. ----------------------------------------------- The URL for this list is: http://p2p.wrox.com/list.asp?list=oracle ORACLE Digest for Wednesday, December 04, 2002. 1. Re: check the duplicate code 2. cursor 3. RE: cursor 4. RE: cursor 5. RE: cursor ---------------------------------------------------------------------- Subject: Re: check the duplicate code From: Xuan Thanh <[email protected]> Date: Tue, 3 Dec 2002 20:22:00 -0800 (PST) X-Message-Number: 1 If you can change value of del field to: (Null = deleted, 0(or any not nul value) = not deleted) then you can create unique key constraint for code and department look: ALTER TABLE QTC_OWNER.department ADD CONSTRAINT A UNIQUE ( code ,del ) / --- loh wai chin <[email protected]> wrote: > is there a way to prevent inserting a record that > with 1 of the fields is > dusplicate with the exsiting record. > > i mean lets consider the following situation: > > i have 1 table tbl_department > > description > > id sys_guid() (primary key) > code nvarchar2(10) > del number(1) > > * the field tbl_department.del is to indicate > whether the record is > already deleted or not. (1 = deleted, 0 = not > deleted) > * tbl_department.code is not a unique key > > > let's said there is already a department record > exist: > id code del > == ==== === > 999.... abc 0 > > > im facing a problem that the system is able to > accept another record with > the same tbl_department.code = abc which i try to > prevent it. > > > is there a way (for example, create a trigger) that > i can check the > duplicate code before inserting into the table? > > > Thanks a lot and regards, > ychin > --- > Change your mail options at > http://p2p.wrox.com/manager.asp or > to unsubscribe send a blank email to %%email.unsub%%. __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---------------------------------------------------------------------- Subject: cursor From: "Natividad Castro" <[email protected]> Date: Wed, 4 Dec 2002 07:34:39 -0500 X-Message-Number: 2 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; / ---------------------------------------------------------------------- Subject: RE: cursor From: "Calhoun, Bob" <[email protected]> Date: Wed, 4 Dec 2002 08:01:22 -0500 X-Message-Number: 3 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. ---------------------------------------------------------------------- Subject: RE: cursor From: "David, Romeo B. (Govt)" <[email protected]> Date: Wed, 4 Dec 2002 09:35:17 -0500 X-Message-Number: 4 You were not re-setting v_fi after you update. Anyway, there is no need to set a cursor for the censusomc table. You just need to loop in table carrier_op_class. do it like this: SET SERVEROUTPUT ON DECLARE cursor c1 is SELECT DOT_NUMBER, CLASSIFICATION_ID FROM CARRIER_OP_CLASS; dot_num c1.dot_number%TYPE := null; v_f1 varchar2(12) := null; begin FOR c1_rec IN c1 LOOP if dot_num is null then dot_num := c1_rec.dot_number; v_f1 := c1_rec.classification_id; else if dot_num <> c1_rec.dot_number then update censusomc set master_field = v_f1 where dot_number = dot_num; dot_num := c1_rec.dot_number; v_f1 := c1_rec.classification_id; else v_f1 := v_f1||c1_rec.classifcation_id; end if; end if; END LOOP; if v_f1 is not null then update censusomc set master_field = v_f1 where dot_number = dot_num; end if; 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%%. ---------------------------------------------------------------------- Subject: RE: cursor From: "David, Romeo B. (Govt)" <[email protected]> Date: Wed, 4 Dec 2002 10:34:47 -0500 X-Message-Number: 5 RE-SENT to add ORDER BY in select statement. You were not re-setting v_fi after you update. Anyway, there is no need to set a cursor for the censusomc table. You just need to loop in table carrier_op_class. do it like this: SET SERVEROUTPUT ON DECLARE cursor c1 is SELECT DOT_NUMBER, CLASSIFICATION_ID FROM CARRIER_OP_CLASS order by DOT_NUMBER,CLASSIFICATION_ID; dot_num c1.dot_number%TYPE := null; v_f1 varchar2(12) := null; begin FOR c1_rec IN c1 LOOP if dot_num is null then dot_num := c1_rec.dot_number; v_f1 := c1_rec.classification_id; else if dot_num <> c1_rec.dot_number then update censusomc set master_field = v_f1 where dot_number = dot_num; dot_num := c1_rec.dot_number; v_f1 := c1_rec.classification_id; else v_f1 := v_f1||c1_rec.classifcation_id; end if; end if; END LOOP; if v_f1 is not null then update censusomc set master_field = v_f1 where dot_number = dot_num; end if; 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%%. --- END OF DIGEST --- Change your mail options at http://p2p.wrox.com/manager.asp or to unsubscribe send a blank email to %%email.unsub%%. --- Change your mail options at http://p2p.wrox.com/manager.asp or to unsubscribe send a blank email to [email protected].