David, Salute your patience.
I would like to be your fan. :-) Regards, Jignesh Makwana On Sep 13, 2011 8:37 PM, "ddf" <[email protected]> wrote: > > > On Sep 12, 4:29 pm, 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. > > Your criteria can't be met the way you have your table defined as > NUMBER columns do not store leading 0's. Changing your table > definition slightly (and your inserts to match the column type): > > SQL> > SQL> CREATE TABLE TEST > 2 ( > 3 MC_NUMBER VARCHAR2(4 BYTE), > 4 PART_A_OR_PART_B CHAR(1 BYTE), > 5 TRANSACTION_CODE NUMBER(3), > 6 MBS_CODE VARCHAR2(2) > 7 ); > > Table created. > > SQL> > SQL> > SQL> INSERT INTO TEST ( > 2 MC_NUMBER, PART_A_OR_PART_B, TRANSACTION_CODE,MBS_CODE > 3 ) > 4 VALUES ( 2121,'A',12, '31' ); > > 1 row created. > > SQL> > SQL> > SQL> INSERT INTO TEST ( > 2 MC_NUMBER, PART_A_OR_PART_B, > TRANSACTION_CODE,MBS_CODE) > 3 VALUES ( 2121, 'B', 11, '01' ); > > 1 row created. > > SQL> > SQL> > SQL> INSERT INTO TEST ( > 2 MC_NUMBER, PART_A_OR_PART_B, > TRANSACTION_CODE,MBS_CODE) > 3 VALUES ( 1111, 'A', 11, '31' ); > > 1 row created. > > SQL> > SQL> > SQL> INSERT INTO TEST ( > 2 MC_NUMBER, PART_A_OR_PART_B, > TRANSACTION_CODE,MBS_CODE) > 3 VALUES ( 1111, 'B', 41, '01' ); > > 1 row created. > > SQL> > SQL> > SQL> INSERT INTO TEST ( > 2 MC_NUMBER, PART_A_OR_PART_B, > TRANSACTION_CODE,MBS_CODE) > 3 VALUES ( 2222, 'A', 28, '30' ); > > 1 row created. > > SQL> > SQL> > SQL> INSERT INTO TEST ( > 2 MC_NUMBER, PART_A_OR_PART_B, > TRANSACTION_CODE,MBS_CODE) > 3 VALUES ( 2222, 'B', 50, '02' ); > > 1 row created. > > SQL> > SQL> > SQL> INSERT INTO TEST ( > 2 MC_NUMBER, PART_A_OR_PART_B, > TRANSACTION_CODE,MBS_CODE) > 3 VALUES ( 3333, 'A', 28, '32' ); > > 1 row created. > > SQL> > SQL> > SQL> INSERT INTO TEST ( > 2 MC_NUMBER, PART_A_OR_PART_B, > TRANSACTION_CODE,MBS_CODE) > 3 VALUES ( 3333, 'B', 11, '02' ); > > 1 row created. > > SQL> > SQL> > SQL> INSERT INTO TEST ( > 2 MC_NUMBER, PART_A_OR_PART_B, > TRANSACTION_CODE,MBS_CODE) > 3 VALUES ( 4444, 'A', 28, '30' ); > > 1 row created. > > SQL> > SQL> > SQL> INSERT INTO TEST ( > 2 MC_NUMBER, PART_A_OR_PART_B, > TRANSACTION_CODE,MBS_CODE) > 3 VALUES ( 4444, 'B', 11, '04' ); > > 1 row created. > > SQL> > SQL> COMMIT; > > Commit complete. > > SQL> > SQL> SELECT * FROM TEST > 2 ORDER BY MC_NUMBER,PART_A_OR_PART_B > 3 > SQL> > SQL> > SQL> select MC_NUMBER , PART_A_OR_PART_B, TRANSACTION_CODE, MBS_CODE > 2 from TEST > 3 where (PART_A_OR_PART_B = 'A' > 4 and TRANSACTION_CODE not in (11, 41) > 5 and MBS_CODE in ('30','31') ) > 6 or (PART_A_OR_PART_B = 'B' > 7 and TRANSACTION_CODE in ('11','41') > 8 and MBS_CODE in (01,02) ) > 9 ORDER BY PART_A_OR_PART_B,MC_NUMBER; > > MC_N P TRANSACTION_CODE MB > ---- - ---------------- -- > 2121 A 12 31 > 2222 A 28 30 > 4444 A 28 30 > 1111 B 41 01 > 2121 B 11 01 > 3333 B 11 02 > > 6 rows selected. > > SQL> > > > David Fitzjarrell > > -- > 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
