The case of the missing patient names
We received complaints that patient names were missing as the top 5% at the beginning of some reports. It was a mystery why patients wouldn't have names in a database focused on patients. Our investigation into the problem led to a number of issues. The first was that EMPI data was not always loaded before the report data was loaded so patients received from other sources with an ID didn't resolve to any known patient in the system. The next issue was that patient data was being displayed using the SQL that essentially looked like this:
select fname + ', ' + lname as patient from patients
SQL concatenates strings using the + sign but if one of the strings is a null then the whole output is a null. Patients loaded into the system through EMPI data have a name broken into parts. But patients loaded through sources that just have the full string are not and store the whole string in the lname field. Those patients were getting zapped by the null value.
One solution to this is to use isnull(fname,'')+ ', ' + isnull(lname,'') and should be considered for any case where a null might appear in a situation where data could be concatenated. Another is to fix the patients table to have a value rather than a null for key columns like this even if it is a blank value like ' '.
The last resort is to change the server settings to SET CONCAT_NULL_YIELDS_NULL { ON | OFF } or before each query... which the data warehouse architect wasn't fond of.