Hi,

How do I test for a TIMESTAMP field being NULL in ElevateDB?

In DBISAM I could use the following:

WHERE activeto <> NULL

Also in the EDBManager a timestamp field without data is shown as NULL,
so I thought it should work.

Cheers!

Phil,

You could try

WHERE activeto IS NOT  NULL

I am new to ElevateDB too, but this works in other DBMS.


Leslie

<< You could try

WHERE activeto IS NOT  NULL

I am new to ElevateDB too, but this works in other DBMS. >>

Lovely job, that did the trick,

Thank you Leslie

Phil,

Leslie is correct, you must use WHERE activeto IS NOT NULL.
According to the SQL 2003 standard, that EDB follows, you can't say that
NULL is equal or different from any other value, not even another NULL,
because NULL means "Unknown" and if a value is not known you can't tell
if it equals some other value or not.

--
Fernando Dias
[Team Elevate]

In some DBMS  "IS [NOT] DISTINCT FROM" can be used to simplify the handling of such
situations.

"Null IS DISTINCT FROM Null"        results to "False"

"Null IS NOT DISTINCT FROM Null" results to "True"

I was not clear enough.   The situation I am refering to is only similar, not the same,
but I think it is worth mentioning here:

There is no problem where  Null is compared to  a Field value as in Phil's question.

Comparsion of Fields which both allowed to have Null values  can be problematic.

Field1=Field2 results to False when Field1 is null and Field2 is null

In many times in application logic all NULLS are equal, so something like this is required
to have proper comparsion:


if Field1=Field2
 or (Field1 =null and Field2 =null) then ...

This is when IS DISTINCT FROM comes handy. I think it is more than being pretty, and
having cleaner source. It saves from the potential problems caused by forgetting properly
handling the NULL values.   

Leslie,

> I was not clear enough.

No problem, I understood what you had in mind and I agree It can be
handy. However, exactly because NULLs can be very confusing, I think the
best approach is to follow the standards or don't use them at all.
That's what I've decided to do in my DB designs: NULLs are allowed and
used strictly "by the book" i.e. following SQL standards, or aren't
allowed at all.

--
Fernando Dias
[Team Elevate]