Re: Oracle NULL vs '' revisited

"Frank van Bortel" <frank.van.bortel_at_gmail.com> wrote in message news:fb1pv1$auu$1_at_news2.zwoll1.ov.home.nl...
>>
>> 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.
>

Quite true, although it is a very standard accounting convention to us the term "Invoice Number" even when the identifier is an alpha-numeric.

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

That just does not follow. The value in question IS known. The value is a function representung the string less its non-numeric characters. How can that be an unknown value if the input string is known? Even if what you say made sense, I hope I don't need to remind you that NULL is no logical substitute for an unknown value.

>
> An empty string would be a known.

Of course.

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

IS NULL isn't enough. To simulate ANSI SQL string comparison in Oracle you would need something like:

a.x = b.x OR (a.x IS NULL AND b.x IS NULL)

-- 
David Portas