Filter records based on multiple columns in oracle

Question

I need to filter specific set of records which meets below condition

Any record which has account as 'N/A' and amount as 0 then that record should be filtered.

Here the issue is there are some records having account as 'N/A' but have amount other than 0 and those records shouldn't be filtered out.

Input

Id   account   amount
1     ABC        5
1     N/A        0
2     PQR        6
2     N/A        0
3     N/A        8
4     N/A        9  

Output

Id   account   amount
1     ABC        5
2     PQR        6
3     N/A        8
4     N/A        9  

Can anyone please help on this with correct filter condition?

Answer 1

account = 'N/A' AND amount = 0 will match the rows that you want to filter out. Assuming that neither of the columns contains NULL values (your sample data does not) then you can use NOT (account = 'N/A' AND amount = 0) to invert that filter to find all the rows you want to keep:

SELECT *
FROM   table_name
WHERE  NOT (account = 'N/A' AND amount = 0);

If your columns can have NULL values and you want to keep them then you can use:

SELECT *
FROM   table_name
WHERE  NOT (account = 'N/A' AND amount = 0)
OR     account IS NULL
OR     amount  IS NULL;

Which, for the sample data:

CREATE TABLE table_name (id, account, amount) AS
  SELECT 1, 'ABC', 5 FROM DUAL UNION ALL
  SELECT 1, 'N/A', 0 FROM DUAL UNION ALL
  SELECT 2, 'PQR', 6 FROM DUAL UNION ALL
  SELECT 2, 'N/A', 0 FROM DUAL UNION ALL
  SELECT 3, 'N/A', 8 FROM DUAL UNION ALL
  SELECT 4, 'N/A', 9 FROM DUAL

Both output:

ID	ACCOUNT	AMOUNT
1	ABC	5
2	PQR	6
3	N/A	8
4	N/A	9

fiddle