What you are written is a rule. Do you want to extract rows which violate this rule ? Or do you want to write an insert trigger that imposes the rule on the table ? Rows violating the rule will not be inserted. Is that what you want to achieve? Regards Gopa On Sep 13, 2011 4:59 AM, "JK" <[email protected]> wrote: > Thanks for the quick response. Actually, the above question was wrong. > Sorry, I was having difficulty in coming up with the right question. > > I have changed the data for insert. Please see the insert statements > below. > > Here is what I want to achieve. > > select mc_number from test > for PART_A_OR_PART_B = 'A' the transaction_code should not be in (11, > 41) and mbs_code should be in (30,31) > and for PART_A_OR_PART_B = 'B' transaction_code should be in (11, 41) > and mbs_code should be in (01,02) > > > CREATE TABLE MYUSER.TEST > ( > MC_NUMBER VARCHAR2(4 BYTE), > PART_A_OR_PART_B CHAR(1 BYTE), > TRANSACTION_CODE NUMBER(3), > MBS_CODE NUMBER(2) > ); > > > INSERT INTO MYUSER.TEST ( > MC_NUMBER, PART_A_OR_PART_B, TRANSACTION_CODE,MBS_CODE > ) > VALUES ( 2121,'A',12, 31 ); > > INSERT INTO MYUSER.TEST ( > MC_NUMBER, PART_A_OR_PART_B, TRANSACTION_CODE,MBS_CODE) > VALUES ( 2121, 'B', 11, 01 ); > > INSERT INTO MYUSER.TEST ( > MC_NUMBER, PART_A_OR_PART_B, TRANSACTION_CODE,MBS_CODE) > VALUES ( 1111, 'A', 11, 31 ); > > INSERT INTO MYUSER.TEST ( > MC_NUMBER, PART_A_OR_PART_B, TRANSACTION_CODE,MBS_CODE) > VALUES ( 1111, 'B', 41, 01 ); > > INSERT INTO MYUSER.TEST ( > MC_NUMBER, PART_A_OR_PART_B, TRANSACTION_CODE,MBS_CODE) > VALUES ( 2222, 'A', 28, 30 ); > > INSERT INTO MYUSER.TEST ( > MC_NUMBER, PART_A_OR_PART_B, TRANSACTION_CODE,MBS_CODE) > VALUES ( 2222, 'B', 50, 02 ); > > INSERT INTO MYUSER.TEST ( > MC_NUMBER, PART_A_OR_PART_B, TRANSACTION_CODE,MBS_CODE) > VALUES ( 3333, 'A', 28, 32 ); > > INSERT INTO MYUSER.TEST ( > MC_NUMBER, PART_A_OR_PART_B, TRANSACTION_CODE,MBS_CODE) > VALUES ( 3333, 'B', 11, 02 ); > > INSERT INTO MYUSER.TEST ( > MC_NUMBER, PART_A_OR_PART_B, TRANSACTION_CODE,MBS_CODE) > VALUES ( 4444, 'A', 28, 30 ); > > INSERT INTO MYUSER.TEST ( > MC_NUMBER, PART_A_OR_PART_B, TRANSACTION_CODE,MBS_CODE) > VALUES ( 4444, 'B', 11, 04 ); > > > > SELECT * FROM TEST > ORDER BY MC_NUMBER,PART_A_OR_PART_B > > Thank you. > > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en
-- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
