Helen and others

I have a table where I have a field COMPLETED_DATE of 'date' type.
When I insert a record to this table I leave this field null.

Then I'd like to have some 'after update' trigger that would fire when
COMPLETED date has changed, so I have tried:

IF (COALESCE(OLD.COMPLETED_DATE,0) <> COALESCE(NEW.COMPLETED_DATE, 0))
THEN ...

I thought that it should work since 'data' type is some kind of
'numeric' type. But I was wrong - and got error as in subject.

Following Helen's suggestion I've tried:
COALESCE(OLD.COMPLETED_DATE, CAST(0 AS DATE))
but with the same result...

I know I'm doing something wrong, but to avoid further 'dark' questions,
I'd like to know what it is.

Thanks
Marcin


W dniu 27.02.2010 22:37, Helen Borrie pisze:

> At 04:33 AM 28/02/2010, you wrote:
>> Hello all
>>
>> Is there a list of datatypes that are not comparable in COALESCE?
>>
>> 'Date' is probably one of them...
>
> That is a "dark" question, Marcin! ;-) What are you trying to do with COALESCE?
>
> You cannot compare anything with NULL, but you can compare a date with a date, i.e., this is OK:
>
> select
> ....,
> coalesce (MyDate, CURRENT_DATE) as SomeDate,
> ....
>
> And SomeDate will be returned as a DATE field. If MyDate is NULL, then SomeDate will be today's date.
>
> The usual rules apply for data type compatibility and use of literals, so the following would not be valid:
>
> select
> ....,
> coalesce (MyDate, 'today') as SomeDate,
>
> You would have to cast the literal:
>
> select
> ....,
> coalesce (MyDate, (cast('today' as DATE))) as SomeDate,
>
> Better that you provide an example of the usage of COALESCE that doesn't work for you so we can figure out why.
>
> ./heLen
>
>