SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Conditional WHERE clause

Top_Turn_BuckleMar 19 2021

Hello Experts,
Version : Oracle 12c
Table Data

<code>

table: test500

ID    TYPE  CODE
1      N    <NULL>
2      N    PPP
3      N    MMM
4      I    <NULL>
5      D    NNN
6      M    OOO
7      I    OOO
8      M    QQQ
9      D   <NULL>

Here <NULL> means NULL.
Conditions:
Do not pick records with TYPE = I.
Records with TYPE = N should be picked up without restrictions, including records with CODE = NULL.
Records with TYPE <> N have the should be picked up with the following restrictions: Do not pick records with CODE = NULL. Do not pick records with CODE IN ('PPP','OOO').
Expected result

ID    TYPE  CODE
1      N    <NULL>
2      N    PPP
3      N    MMM
5      D    NNN
8      M    QQQ

My Effort

select * from test500
where type <> 'I'
and code is not null
and code not in ('PPP','OOO')
union
select * from test500
where type = 'N';

Could you please suggest a way where we can eliminate the UNION and yet have all conditions satisfied and have the expected output.
Thank You.

This post has been answered by Frank Kulash on Mar 19 2021

Jump to Answer

Added on Mar 19 2021

4 comments

3,333 views

1 mention