Search code examples
oracleoracle-sqldeveloper

Filtering columns only "if" certain condition is satisfied

I am having table as below

enter image description here

And I want output as below

enter image description here

I tried doing something as below

SELECT * FROM TABLE_NAME WHERE COLUMN2 = 'PQR' AND COLUMN3 IS NOT NULL

But it also removes 3rd and 4th row from 1st table. Whereas in output of the query I need to remove only rows which are having Column2 as PQR and Column3 as NOT NULL

I also tried to use case statements but I'm not able to get actual idea of how to implement it.

EDIT 1:-

Also I thought of trying one more thing now.

SELECT * FROM TABLE_NAME WHERE COLUMN2 IN ('PQR', 'XYZ', 'RST') AND COLUMN3 IS NOT NULL

But the problem is in actual table COLUMN2 is having more than 150 distinct values which I can't go on mentioning in IN clause.

Solution
    1. not(COLUMN2 = 'PQR' AND COLUMN3 IS NOT NULL)

    Example:

    SQL> ;
  1  with -- test data:
  2   t(column1,column2,column3) as (
  3     select 'ABC','PQR',cast(null as int) from dual union all
  4     select 'DEF','PQR',123 from dual union all
  5     select 'GHI','XYZ',cast(null as int) from dual union all
  6     select 'JKL','RST',cast(null as int) from dual
  7  ) -- test query:
  8  select *
  9  from t
 10* where not(COLUMN2 = 'PQR' AND COLUMN3 IS NOT NULL)
SQL> /

COL COL    COLUMN3
--- --- ----------
ABC PQR NULL
GHI XYZ NULL
JKL RST NULL

    or lnnvl(COLUMN2 = 'PQR') or COLUMN3 IS NULL

    Example:

    with -- test data:
 t(column1,column2,column3) as (
   select 'ABC','PQR',cast(null as int) from dual union all
   select 'DEF','PQR',123 from dual union all
   select 'GHI','XYZ',cast(null as int) from dual union all
   select 'JKL','RST',cast(null as int) from dual 
) -- test query:
select * 
from t
where lnnvl(COLUMN2 = 'PQR') or COLUMN3 IS NULL;

COL COL    COLUMN3
--- --- ----------
ABC PQR NULL
GHI XYZ NULL
JKL RST NULL