Re: Update Status Field after Expiry Date

--CELKO-- wrote:

It seems like dropping UNKNOWN would leave a sensible set of rules:

and | T N F or | T N F not |
----+------ ---+------ ----+--
T | T N F T | T T T T | F
N | N N F N | T N N N | N
F | F F F F | T N F F | T

Am I overlooking anything? <<

The NULL propagation rule.

and | T N F or | T N F not |
----+------ ---+------ ----+--
T | T N F T | T N T T | F
N | N N N N | N N N N | N
F | F N F F | T N F F | T

This means that TRUE OR NULL = NULL, etc. and you can now prove that
TRUE = FALSE. The UNKNOWN logical value does not have this behavior
and that is why we have it.

NULL represents the concept "unknown" in all other contexts; it should
represent it in the context of the Boolean data type as well.

IINM, while SQL doesn't have a mandatory Boolean *type*, it already
follows TRUE OR NULL = TRUE and FALSE AND NULL = FALSE in Boolean
*expressions*. Example:

CREATE TABLE Table1 (Column1 varchar(10), Column2 varchar(10))

INSERT INTO Table1 (Column1, Column2) values ('A' , null)
INSERT INTO Table1 (Column1, Column2) values (null, 'B' )

SELECT * FROM Table1 WHERE Column1 = 'A' OR Column2 = 'B'
-- returns 2 rows

SELECT * FROM Table1 WHERE Column1 = 'A' AND Column2 = 'B'
-- returns 0 rows
.

Relevant Pages

  • Re: Using expressions in a SELECT Clause
    ... There is no BOOLEAN data type in SQL. ... SQL-92 has no boolean data type, but the current SQL standard ... type also supports the unknown truth value as the null value. ...
    (microsoft.public.sqlserver.programming)
  • Re: On exit event
    ... Did Access Basic have a Boolean data type in its first incarnation? ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.modulesdaovba)
  • Re: [Info-Ingres] Why BOOLEAN data type not provided in Ingres
    ... Why BOOLEAN data type not provided in Ingres ... AFAIK, the only ... mainstream DB that supports it ...
    (comp.databases.ingres)
  • Re: Comparison operator in Select list
    ... SQL Server does not support a boolean data type, ... A boolean expression can only be used in an IF statement, ...
    (comp.databases.ms-sqlserver)
  • Re: Is this C program doing what it is supposed to do ?
    ... Boolean values and expressions in contexts other than explicit ... Also, as posts in this thread have shown, what is clever but obscure to one programmer may be idiomatic and immediately clear to another; and what is clear and simple to one programmer may be annoyingly verbose to another. ... Whether the result of an equality operator is stored in a variable, or results in either of two lines being executed, it is equally debuggable. ... Same if `foo` and `bar` were, in fact, complex expressions involving function calls. ...
    (comp.lang.c)