Unable to retrieve a record containing NULL value [message #681482] |
Wed, 22 July 2020 09:30 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
Hello all,
I am unable to retrieve a record from a table that contains NULL value despite me put the condition correctly.
For example, I am using the query below:
SELECT * FROM barcode B
WHERE B.business_unit_id = 65
AND B.style_id = 'GD5569'
AND B.dimension_id IS NULL;
In case I do not use NULL then the following records are obtained and I am interested in the 2 records displayed in the attachment.
Is there anything that I am doing wrong?
-
Attachment: 1.jpg
(Size: 94.06KB, Downloaded 1495 times)
|
|
|
|
Re: Unable to retrieve a record containing NULL value [message #681484 is a reply to message #681483] |
Wed, 22 July 2020 09:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 22 July 2020 09:44
Quote:Is there anything that I am doing wrong?
That or Oracle has a bug.
The same data resides in another table called BAR_CODES in a different schema and when I query it using the below, I get those 2 records but of course it's from a different table. How can it be a bug?
select * FROM bar_codes B
WHERE B.business_unit_id = 65
AND B.style_id = 'GD5569'
AND B.size_id = 'L'
--and NVL(b.dimension_id,'n/a') = 'n/a';-- IS NULL;
AND (B.dimension_id IS NULL
OR B.dimension_id IS not NULL);
|
|
|
|
|
|
Re: Unable to retrieve a record containing NULL value [message #681502 is a reply to message #681486] |
Fri, 24 July 2020 13:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 22 July 2020 10:33
This is why we always ask for a test case we can reproduce.
Most of the time building it you see the error.
I do not know if with the data we could have reproduced the error because the data that is being sent from one schema (the one that I was working upon) has missing values as NULL, whereas the other schema (where I was experiencing the issue) are sending ' '(space) instead of a NULL value.
However in larger context I do see your point for a test case.
|
|
|
|