CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Zeb-ur-Rehman
Zeb-ur-Rehman

Reputation: 1209

Oracle: Get All Rows Except One

I am stuck with a simple query. What i want is to get all rows except one Kindly have a look at the following data.

COL_A   COL_B
B       D
B       (null)
B       C
G       D
G       (null)
G       C

I want to get all rows except but B C. Kindly have a look at the sqlfiddle

I have tried to get the rows by anding col_A <> 'B' and col_B <> 'C' but it's not anding the operation. Your help will be much appreciated.

Thanks

Upvotes: 5

Views: 2498

Answers (6)

user2672165
user2672165

Reputation: 3049

One possible solution. Maybe not the most elegant:

select req_for col_A, doc_typ col_B 
from a
where (req_for IS NULL OR doc_typ IS NULL) 
OR (req_for,doc_typ) 
NOT IN (select 'B','C' from dual);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Your problem is the NULL values. Here is a concise way of expressing this in Oracle:

where (col_A || col_B) <>  'BC'

Oracle treats NULL values as the empty string in string concatenation.

Upvotes: 1

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

select req_for col_A, doc_typ col_B from a
where req_for||doc_typ != 'BC';

select req_for col_A, doc_typ col_B from a
where case when req_for = 'B' and doc_typ='C' then 0 else 1 end > 0

Upvotes: 0

vc 74
vc 74

Reputation: 38179

Try

where not(col_A = 'B' and col_B = 'C')

or

where col_A <> 'B' or col_B <> 'C'

Upvotes: 1

Ollie
Ollie

Reputation: 17538

Could you not just remove the unwanted row:

select req_for col_A, doc_typ col_B from a
where NOT (NVL(doc_typ,'NA') = 'C' AND nvl(req_for,'NA') = 'B');

Upvotes: 0

Steven Eccles
Steven Eccles

Reputation: 239

Ah, negatives. Always causing trouble. With a SQL query like this you have to think about what you want to INCLUDE, not what you want to EXCLUDE.

If you do where nvl(doc_typ,'NA') <> 'C' and nvl(req_for, 'NA') <> 'B';, you aren't including any rows with doc_type of C, and you aren't including any rows with req_for of B.

You want to do where nvl(doc_typ,'NA') <> 'C' or nvl(req_for, 'NA') <> 'B';. This way a doc_type of C will still be included, as long as its req_for isn't also B.

Upvotes: 0

Related Questions