Re: How to cope with missing values - NULLS?

"Peter Koch Larsen" <pkl_at_mailme.dk> wrote in message news:61c84197.0304181445.46842baa_at_posting.google.com...
> [snip] Let us say that we have
> defined a view as an outer join: columns col1 and col2 are "common,
> but col3 and col4 might be missing. Now let's query that view - we are
> only interested in tuples, where col4 is larger than 10: SQL gives us
> something like
>
> SELECT * FROM ojview WHERE col4 > 10
>
> How would you phrase such a query in your (hypothetical) relational
> system?

Well col4 would not be of type say INTEGER, but of type say INTEGERuUNK and I would not expect the ">" operator to be defined for type INTEGERuUNK.

So I guess the query would something like be

SELECT * FROM ojview WHERE col4 <> UNK
and (IF IS_INTEGER(col4) THEN CAST_DOWN_AS_INTEGER(col4) > 10 ELSE TRUE)

now I'll admit that my syntax above is a little long winded, and better suggestions are encouraged, but it conveys the idea (I hope) and advised run time errors from the CAST_DOWN. Looking at Date & Darwen's very strong suggestion #8 : Special default values, they don't have a syntax suggestion for the above, but do have many other useful observations on this topic.

> My personal conclusion is that we are stuck with NULLS - if not in the
> SQL sense then at least in the sense that we are to have some kind of
> sum type. And if we are stuck with such a type, then the only sensible
> thing is to have it standardised by the DBMS - how else are we going
> to let the DBMS perform the outer join by itself?
>

Using type inheritance, some UNK value could be 'standardised' for outer joins where the user does not specify their own defaults. In otherwords you *could* define a 'union' type that includes the UNK value for every other scalar type in your system. E.g. BOOLEAN_UKN, STRING_UKN, ... . Again I refer you to The Third Manifesto

The question just then remains whether such an UKN value should be 'standardised'. Well if we can all agree what the meaning of UNK is, and that it means the same for every application and for every use of the term, like say the value 42 has an agreed meaning, then I would be happy to standardise it.
However, I don't think that such an agreed meaning could be arrived at. At the very least I would suggest that a database schema that includes some generically defined UNK value is not a schema that has very well designed.

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Sat Apr 19 2003 - 23:40:33 CEST