Oracle SQL SELECT unpopulated records with the "NOT NULL" constraint

Question

In order to retrieve the data I need, I must SELECT records where there is no data. Some of the rows are unpopulated(?).

Many of these "unpopulated" rows are set with the Not Null constraint. This makes things difficult! I cannot simply search for NULL rows because they are NOT NULL.

I have been able to select or exclude unpopulated rows with a few methods. These methods seem to randomly work or not work.

Example: select or exclude records where st.sart_code or st.sart_hold or st.sart_status or st.sart_date is unpopulated.

SELECT 
sp.sprite_id, sp.sprite_last, sp.sprite_first,
st.sart_code 
/* 4 data retrieval methods are listed below.  
For st.sart_code, I have substituted:
st.sart_hold, st.sart_status, and st.sart_date in the methods 2-4*/

FROM
sprite sp
JOIN sart st
on sp.sprite_pidm = st.sart_pidm

METHOD 1 - select records with rows that do not have the value EVEA -- st.sart_code could contain multiple values for one sp.sprite_id. This is a checklist of items. I am looking for records that do not have EVEA in the checklist

Varchar2 type with a Not Null constraint

WHERE
Sp.sprite_change_ind is null
and
st.sart_pidm NOT IN
(SELECT st.sart_pidm
FROM sart st
WHERE st.sart_code = 'EVEA')

METHOD 2 - select records with rows that do not have the value A2 -- st.sart_hold could contain multiple values for one sp.sprite_id. st.sart_hold may be blank/unpopulated (record has no holds) or contain several different holds. The values are account hold types. I am looking for records that do not have that particular "A2" hold.

Varchar2 type with a Not Null constraint

EDIT I just realized that this works ONLY if there is at least one hold already. If the person has no holds, this script will not select the records (even though the person also has no A2 hold).

WHERE
Sp.sprite_change_ind is null
and
group by sp.sprite_id, sp.sprite_last, sp.sprite_first, st.sart_hold
having sum(case when st.sart_hold = 'A2' then 1 else 0 end) = 0;

METHOD 3 - select records with rows that have no value for st.sart_status -- st.sart_status could contain only 1 of 3 possible values or NO value for one sp.sprite_id. The values are file statuses. I am looking for records that have no status

Varchar2 type with a Not Null constraint

WHERE
Sp.sprite_change_ind is null
and
trim(st.sart_status) is null

METHOD 4 - select records with rows that are NOT missing ANY values in st.sart_date (all date fields in list are populated) -- st.sart_date could either contain a date or be blank/unpopulated for one sp.sprite_id. The value is a received date for a checklist item. I am excluding ANY record that has no date for any of the checklist items (there may be many items with corresponding dates).

Date type with a Not Null constraint

This is a little different, so I am including the first part again.

with MYVIEW AS
( 
SELECT 
sp.sprite_id AS Per_ID
sp.sprite_last, 
sp.sprite_first,
st.sart_date as RECEIVED_DATE

FROM
sprite sp
JOIN sart st
on sp.sprite_pidm = st.sart_pidm

WHERE
Sp.sprite_change_ind is null
)

Select 
Per_ID as "ID", 
max(SPRITE_LAST_NAME) as "Last", 
max(SPRITE_FIRST_NAME) as "First",

FROM MYVIEW
GROUP BY Per_ID
HAVING SUM(NVL2(RECEIVED_DATE,0,1)) = 0

My questions: I have had a difficult time finding methods of working with Not Null constraint fields.

EDIT: How do I see what is in the "not null" constrained field when it is not populated?

1. Why do the methods above not always work when looking for unpopulated fields? Do certain methods only work with certain data types (varchar2, number, date)? Or does it have to do with the type of JOIN I use? Something else?

2. Are there other methods out there someone could please direct me to? Any guidance would be greatly appreciated!

3. What is the correct terminology for "selecting records where there are unpopulated fields of [ColumnName DataType() NOT NULL]?" If I knew the terminology for what I am trying to ask, I could search for it.

NOTE My scripts are usually MUCH more involved than the examples above. I usually have at least 3 joins and many WHERE clauses.

Please let me know if this question is too involved! I am new here. :-)

Answer 1

Probably more a long comment than an answer, but since there isn't much activity here...

Oracle SQL SELECT blank records with the “NOT NULL” constraint _{Auntie Anita}

---

- How do you have blanks if they are not null - is that partly what you're asking? _{Alex Poole}
- That is one of the problems. _{Auntie Anita}

Few things to know:

1. In Oracle the empty string '' is the same thing as NULL for VARCHAR/CHAR. That's a departure from "standard" SQL that makes a distinction between empty strings and NULL strings.
2. TRIM will return NULL for NULL/empty strings/space only strings.
3. but strings composed of spaces/invisible characters are not null. Even if they only contains the character CHR(0) (aka NUL -- with only one L )
4. and TRIM does not remove invisible characters. Only spaces.

To convince yourself, try those:

select NVL2(CAST('' AS VARCHAR2(20)), 'NOT NULL','NULL') FROM DUAL
select NVL2(CAST('' AS CHAR(20)), 'NOT NULL','NULL') FROM DUAL
select NVL2(TRIM('  '), 'NOT NULL','NULL') FROM DUAL
select NVL2(' ', 'NOT NULL','NULL') FROM DUAL
select NVL2(CHR(10), 'NOT NULL','NULL') FROM DUAL
select NVL2(CHR(0), 'NOT NULL','NULL') FROM DUAL
select NVL2(TRIM('  '||CHR(10)), 'NOT NULL','NULL') FROM DUAL
select NVL2(TRIM('  '||CHR(0)), 'NOT NULL','NULL') FROM DUAL

So, my guess is your "not null empty fields" in fact contain either some invisible characters -- or maybe even a single CHR(0). This is quite possible as in some languages, the NUL character is used as string terminator -- and might have sneaked into your DB at data import time for empty/missing values. Intentionally or not.

To check for that, you might want to try RAWTOHEX to examine your suspect data fields. In the following example, notice how the middle NUL character is lurking unnoticed when displayed as a string. But not in the raw hex dump:

SQL> select 'abc' || chr(0) || 'def' AS str, 
           RAWTOHEX('abc' || CHR(0) || 'def') AS hex FROM DUAL


STR                     HEX
abcdef                  61626300646566
^^^^^^                        ^^
Is there something           Yes !
special here?

---

Please let me know if this question is too involved! I am new here. :-)

:D "StackOverflow" is usually much more efficient if you are able to narrow down your issue. Ideally providing some reproducible case (formelly know as SSCCE or MCVE).

Take time to examine closely your data, and if needed, don't hesitate to post an other more focused answer.