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: 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 489 times)

Report message to a moderator

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: 68421
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.

Report message to a moderator

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: 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);

Report message to a moderator

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: 282
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;

Report message to a moderator

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: 68421
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.

Report message to a moderator

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: 1375
Registered: September 2013
Senior Member
Your question was answered at OTN
https://community.oracle.com/thread/4340016

Report message to a moderator

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: 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.

Report message to a moderator

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: 282
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).

Report message to a moderator

Previous Topic: API for Progress Line
Next Topic: Select Query suggestion
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Mar 29 12:16:06 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.