Home » SQL & PL/SQL » SQL & PL/SQL » Unable to retrieve a record containing NULL value (Oracle forms 11g -32 bit on Windows 7 professional)
Unable to retrieve a record containing NULL value [message #681482] Wed, 22 July 2020 09:30 Go to next message
buggleboy007
Messages: 261
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 63 times)
Re: Unable to retrieve a record containing NULL value [message #681483 is a reply to message #681482] Wed, 22 July 2020 09:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Is there anything that I am doing wrong?
That or Oracle has a bug.

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 messageGo to next message
buggleboy007
Messages: 261
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 #681485 is a reply to message #681484] Wed, 22 July 2020 09:57 Go to previous messageGo to next message
buggleboy007
Messages: 261
Registered: November 2010
Location: Canada
Senior Member
The issue has been fixed. It seems that it's a space instead of NULL. That was really strange. Using assistance from Oracle forums, the issue has been fixed:
https://community.oracle.com/message/15659802#15659802
select *  FROM barcode 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 TRIM(B.dimension_id) IS  NULL;

Re: Unable to retrieve a record containing NULL value [message #681486 is a reply to message #681485] Wed, 22 July 2020 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is why we always ask for a test case we can reproduce.
Most of the time building it you see the error.

Re: Unable to retrieve a record containing NULL value [message #681488 is a reply to message #681482] Wed, 22 July 2020 12:19 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
Your question was answered at OTN
https://community.oracle.com/thread/4340016
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 messageGo to next message
buggleboy007
Messages: 261
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.
Re: Unable to retrieve a record containing NULL value [message #681503 is a reply to message #681488] Fri, 24 July 2020 13:14 Go to previous message
buggleboy007
Messages: 261
Registered: November 2010
Location: Canada
Senior Member
EdStevens wrote on Wed, 22 July 2020 12:19
Your question was answered at OTN
https://community.oracle.com/thread/4340016
of course it was with friends from OTN. I did include the reference (just that I mentioned Oracle forum instead of OTN).
Previous Topic: API for Progress Line
Next Topic: Select Query suggestion
Goto Forum:
  


Current Time: Fri Sep 18 03:35:27 CDT 2020