PT 8.62

Randall Groncki

Summary

In a PeopleSoft database, only date and datetime fields are allowed to be Null.  All other field types are defined as “Not Null”, and we put values such as a space or zero in the field if it has no value.

If this date field is a key, good design is that it should be required and not null.

A problem with null values in an Oracle database table is that they can’t accurately be compared with anything, even themselves.

Null is Special

Null is not a space character or zero.  It’s an odd form of nothing.  This is why SQL has a special identifier for null values: null

However, comparing a null directly to another null is always false, even if the comparing the same field against itself.

Null = Null  (False)

Null <> Null  (False)

Example From the PeopleSoft HCM Demo Database

select j.emplid, j.empl_rcd, j.effdt, j.effseq, j.position_nbr, j.position_entry_dt
  from ps_job j
where j.effdt = (select max(j2.effdt) from ps_job j2
            where j2.emplid = j.emplid
            and j2.empl_rcd = j.empl_rcd
            and j2.effdt <= sysdate)
and j.effseq = (select max(j3.effseq) from ps_job j3
            where j3.emplid = j.emplid
            and j3.empl_rcd = j.empl_rcd
            and j3.effdt = j.effdt)
and j.empl_status = 'A'
and j.emplid like 'KU0316';

Running this example results in the following results: one row of data where the “POSITION_ENTRY_DT” field is Null

Now, let’s compare that null POSITION_ENTRY_DT field to itself.  IF the value wasn’t null, it should always equal itself.

select j.emplid, j.empl_rcd, j.effdt, j.effseq, j.position_nbr, j.position_entry_dt
  from ps_job j
where j.effdt = (select max(j2.effdt) from ps_job j2
            where j2.emplid = j.emplid
            and j2.empl_rcd = j.empl_rcd
            and j2.effdt <= sysdate)
and j.effseq = (select max(j3.effseq) from ps_job j3
            where j3.emplid = j.emplid
            and j3.empl_rcd = j.empl_rcd
            and j3.effdt = j.effdt)
and j.empl_status = 'A'
and j.emplid like 'KU0316'
and j.position_entry_dt = j.position_entry_dt;

So if POSITION_ENTRY_DT does not equal itself, let’s test it that way

select j.emplid, j.empl_rcd, j.effdt, j.effseq, j.position_nbr, j.position_entry_dt
  from ps_job j
where j.effdt = (select max(j2.effdt) from ps_job j2
            where j2.emplid = j.emplid
            and j2.empl_rcd = j.empl_rcd
            and j2.effdt <= sysdate)
and j.effseq = (select max(j3.effseq) from ps_job j3
            where j3.emplid = j.emplid
            and j3.empl_rcd = j.empl_rcd
            and j3.effdt = j.effdt)
and j.empl_status = 'A'
and j.emplid like 'KU0316'
and j.position_entry_dt <> j.position_entry_dt;

This also returns no rows

How to compare potentially Null Values

In Oracle SQL, the way to compare a field that is potentially NULL against another field is to use the NVL() function.   This function tests a value for a Null and converts it to a safe value.  If the field value isn’t a Null, it returns that value.

Since you get to determine what value is returned if the field is NULL, ensure you use a value that you don’t expect to encounter in your data to guard against accidental matches.

Here is our statement using the NVL() function.

select j.emplid, j.empl_rcd, j.effdt, j.effseq, j.position_nbr, j.position_entry_dt
  from ps_job j
where j.effdt = (select max(j2.effdt) from ps_job j2
            where j2.emplid = j.emplid
            and j2.empl_rcd = j.empl_rcd
            and j2.effdt <= sysdate)
and j.effseq = (select max(j3.effseq) from ps_job j3
            where j3.emplid = j.emplid
            and j3.empl_rcd = j.empl_rcd
            and j3.effdt = j.effdt)
and j.empl_status = 'A'
and j.emplid like 'KU0316'
and  nvl(j.position_entry_dt, to_date('1801-01-01','YYYY-MM-DD')) =   nvl(j.position_entry_dt, to_date('1801-01-01','YYYY-MM-DD'));

This correctly returns our row of data

Notice that I’m setting my value if null to January 1, 1801 if the field is null.   I don’t expect any of our current employees to have that date in their dataset.

Just to check the inverse, here is the statement checking if the Null values does not equal itself as was the case of comparing the Null values directly.

select j.emplid, j.empl_rcd, j.effdt, j.effseq, j.position_nbr, j.position_entry_dt
  from ps_job j
where j.effdt = (select max(j2.effdt) from ps_job j2
            where j2.emplid = j.emplid
            and j2.empl_rcd = j.empl_rcd
            and j2.effdt <= sysdate)
and j.effseq = (select max(j3.effseq) from ps_job j3
            where j3.emplid = j.emplid
            and j3.empl_rcd = j.empl_rcd
            and j3.effdt = j.effdt)
and j.empl_status = 'A'
and j.emplid like 'KU0316'
and  nvl(j.position_entry_dt, to_date('1801-01-01','YYYY-MM-DD')) <>   nvl(j.position_entry_dt, to_date('1801-01-01','YYYY-MM-DD'));

Correctly result is an empty return

Wrap Up

If you have a requirement to compare fields which can be null, use the NLV() function on potentially null fields to avoid incorrect comparisons.