Re: Oracle NULL vs '' revisited

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1188150665.582294_at_bubbleator.drizzle.com...
> William Robertson wrote:
>> On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
>> wrote:
>>> NULL <> NULL, but '' = '', if that would help.
>>> The length of NULL is NULL again.
>>
>> If we had a '' in Oracle then its length would be 0, but we don't,
>> which is the subject of this thread. I was hoping someone could
>> provide an example of non-null '' being a useful value.
>

Here are two examples, one real and one hypothetical.

I used to work for an audit firm. One of our services was to analyse accounts payable data to look for duplicate payments. In support of this, various statistics and exception reports were produced from the clients' financial systems. Typically, the financial systems we dealt with implemented the business rule that the combination of payee and document number (such as an invoice, debit or credit note) had to be unique. So the only way for a document to be processed twice was if the document was entered with a different number, perhaps due to a keying error or due to an operator forcing a payment through by modifying the number in some way, for example by entering "123A" instead of "123".

As part of the audit, one of the transforms we did was to "clean" invoice and credit note numbers by removing non-numeric characters from the unique document number - so "123A" would become "123" and could be matched automatically with any similarly numbered document. Inevitably there was a lot of surplus "noise" generated by this technique - we were usually dealing with millions of invoices and many false matches were unavoidable - but it did produce the valid result that the column containing a cleaned document number would be blank if the original didn't contain any numeric characters. Those cases were probably of no interest most of the time but we certainly wouldn't be allowed to exclude them. Converting them to nulls wouldn't be acceptable either because the consumers of the data were accountants, who probably didn't know what a null was. And why should they? They just wanted to peform their usual queries and analysis, which might well include joins on the cleaned number column. Nulls would have been nothing like equivalent to using an empty string and would have added needless complexity.

Now the hypothetical scenario. User names and passwords stored in a table and used for ODBC / OLEDB connection strings. Depending on the provider, a password may have to be supplied, even if it is a blank one. I do not advocate using blank passwords, but a blank password may be necessary or unavoidable in some cases (for example over a trusted connection, where the target system can't support the appropriate password management). Why should I be forced to make the password column nullable when the valid and correct value is a zero-length string?

> In the end you only have three values, NULL, 0
> and > 0. There are a nearly unlimited ways to code this in any
> language and in any tool.

I agree (I assume 0 refers to a zero-length string). I don't agree that zero-length strings are "empty" or not useful or that nulls are always an acceptable substitute.

-- 
David Portas