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
