Re: The BOOLEAN data type

71062.1056_at_compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.0304031753.6bc55cbf_at_posting.google.com>...
> >> In that case the SHIPPED attribute should be a derived attribute.
> But
> you can not do that with SQL. <<
>
> Unh??? Assuming the usual trick of a NULL for an unknown future
> date:
>
> SELECT order_nbr, CASE WHEN shipped_date IS NOT NULL
> THEN 'yes' ELSE 'No ' END AS shipped_flag
> FROM Orders;

But 'yes' is not a boolean value, it is a character string.

If you want the relation of unshipped orders then you need a little trick:

select * from orders where shipped_flag = 'No ';

With a "Date DBMS" it could be:

orders where not shipped;

A lot more elegant and less error prone.  

> >> In a recent database design I used a PRINTED boolean attribute, and
> the customer is not interested on the actual print date. <<
>
> Yet; wait until he has to do an audit trail. People almost alway ask
> "When" if they are talking about an event that has legal consequences.

It is not the case, at least in my country.

> >> Derived attributes and integrity constraints are for that. <<
>
> So, we put redundancy in the table design, then add extra constriants
> to maintain it? Bad idea!

It is not a bad idea if the redundancy is controlled by the DBMS, like when you use views.

For instance: the actual stock of an article is redundant data, but I would like to do this:

select id, actual_stock from articles;

Of course we don't need redundancy on base relvars in order to do that.

Regards
  Alfredo Received on Fri Apr 04 2003 - 12:13:34 CEST