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
