like Michael and Jignesh said:
SELECT *
FROM TEST
WHERE (
PART_A_OR_PART_B = 'A'
AND TRANSACTION_CODE NOT IN (11, 41)
AND MBS_CODE IN (30, 31)
)
OR
(
PART_A_OR_PART_B = 'B'
AND TRANSACTION_CODE IN (11, 41)
AND MBS_CODE IN (01, 02)
);
On Mon, Sep 12, 2011 at 7:29 PM, JK <jyka...@gmail.com> 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 Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> 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 Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
