Re: Oracle NULL vs '' revisited

On Aug 26, 2:42 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
> "DA Morgan" <damor..._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

David,

I notice in both descriptions you mention "blank" results or data

> 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.
Now in this case you likely really do end up with '' (as opposed to ' '), but the intention is the same. a cleaned value implies a result that is blank. (Consider a CHAR versus VARCHAR2 datatype). So you might need some extra code to deal with this case, but you want a blank not an "empty" string.

> password may have to be supplied, even if it is a blank one. I do not
And here it is pretty much how the UI might be coded. If the user merely presses <enter> at the password field, the error back to him would be something like: Password Required. Eventually they would enter a blank (' ') and the UI is happy, and the DB is happy (you can encrypt ' ' easily).

I understand the frustation. If you are a UNIX programmer, used to things like /dev/null (such a wonderful creation), then you just want '' to be there. But notice, even in C from the user interface you will not really get just "" as the input. There is no input until the user presses <enter>, so the raw input always contains at least "\n". That \n might be stripped out but it was there.

So I still don't see much use for '' except for maintaining some mathematical properties on strings (' ' is a poor identity operand).

  Ed Received on Mon Aug 27 2007 - 12:49:56 CDT