Re: Oracle NULL vs '' revisited

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

David Portas wrote:
> "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.
>

[snip]
>
> operator forcing a payment through by modifying the number in some way, for
> example by entering "123A" instead of "123".
>

That's a bad design (or choice of words from your side) to begin with. That is not an invoice number, it's an invoice identifier.
Numbers: [0-9] - not an "A" to be seen.

> 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.

If it were a true number, the result would be NULL. As described, in Oracle, it would be an empty character field, or NULL. See the beauty? Both cases would be NULL, undefined, not known.

An empty string would be a known.
> 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

Well - do they understand how long cables introduce capacity, so hat your disk cables are limited in length? No - and they don't need to know either. You could have explained the invoice codes simply are unknown. And that is exactly what it is.
> 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.

Oh, come on! where ... = '' is less complex than where ... is null?

[snip]
>
> 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.
>

or the other way around :)
- --
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFG1GshLw8L4IAs830RAiW8AJ9cUcWXfGGHp2wE1Snm+ZGT07HCZgCeOtRX F4SnC9mcmbbVveaCj4dyzbI=
=As3v
-----END PGP SIGNATURE----- Received on Tue Aug 28 2007 - 13:36:17 CDT