Re: Oracle NULL vs '' revisited

"Frank van Bortel" <frank.van.bortel@xxxxxxxxx> wrote in message
news:fb1pv1$auu$1@xxxxxxxxxxxxxxxxxxxxxxxxxx

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


.

Relevant Pages

  • Re: jtds driver and SQL Server performance problem
    ... The slow query is retreiving the row based on a "char" column. ... } catch (SQLException e) ... invoice = checkInvoice; ... public void writeInvoice(int docid, int objectid, String invoice) ...
    (comp.lang.java.databases)
  • Re: help please! reading text re post
    ... Dim Buffer As String ... get stuff like invoice date, ... Declare 3 integer variable (e.g. posi As Integer, t As Integer, ...
    (microsoft.public.vb.general.discussion)
  • Re: Form Calculation writes record to Audit Log
    ... Enum enumLogTypes 'Keep in sync with tblLogTypes ... For example, on an invoice, if the user changes the ... Public Function AuditLog(LogType As String, ...
    (comp.databases.ms-access)
  • Re: Understanding Public Module or Class
    ... Public shared myPublic as String ... Dim Invoice As Decimal ... Dim Wage As Decimal ... problems if I have textboxes named the same in two diffrent forms? ...
    (microsoft.public.dotnet.general)
  • Re: Trimming and clensing a string
    ... The following function will remove all non-numeric characters from a string: ... Dim BytesOut() As Byte ... Dim LenBytes As Integer ...
    (microsoft.public.access.formscoding)