Re: Oracle NULL vs '' revisited

"DA Morgan" <damorgan@xxxxxxxxx> wrote in message
news:1188150665.582294@xxxxxxxxxxxxxxxxxxxxxxxxx
William Robertson wrote:
On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor...@xxxxxxxxx>
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


.

Relevant Pages

  • Re: Oracle NULL vs revisited
    ... the financial systems we dealt with ... to using an empty string and would have added needless complexity. ... I agree (I assume 0 refers to a zero-length string). ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.oracle.server)
  • Re: Criteria to return all records if selection from form is null
    ... Add a zero-length string to the field. ... I'm trying to pass multiple query criteria from form controls. ... And this works fine for the fields where there are no Nulls in the data, but if there are Nulls in this field I don't get those records. ...
    (microsoft.public.access.queries)
  • Re: Set value as null or ""?
    ... because you're converting any Nulls in the field to a ... zero-length string before making your comparison. ... ZLS in the field prevents the use of SQL criteria like ...
    (microsoft.public.access.formscoding)