Subject Re: [firebird-support] Null to Zero conversion
Author Cavit Aladag
Hi all,
Thanks for your precious help.
Martijn, I solved the problem using COALESCE. About the union thing, I have tried it before posting my question but I got the following error message:
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Invalid command.
Data type unknown.
Nikolay the query is the same as you suggested. i.e:"select col1, col2, 0 where col3 is NULL from table unioun
select col1, col2, col3 where col3 is NOT NULL from table"
It seems pretty logical to me at the first sight but I don't know where the error is. I tried replacing '0' with '0.00', but still the same. I also tried CAST('0.00' as DOUBLE), but it is nonsense as it is used in where clause. I really want to know the reason for this error.
Paul, it was not an updateable view so I could not use update but trigger will be a good idea.
Cavit Aladag

----- Original Message -----
From: Martijn Tonies
Sent: Thursday, August 28, 2003 5:34 PM
Subject: Re: [firebird-support] Null to Zero conversion

Hi Cavit,

> > I have a view returning null values because of left join but I want
> > to replace null values with 0's. I cannod find any way to do this. I
> > think there is no function like IsNull(). I tried using union but I
> > get an error message of "datatype unknown".

With Fb1.5 - use COALESCE, which can do an IsNull.

Or use an UNION with:

CAST(NULL as <datatype of your column>)

With regards,

Martijn Tonies
Database Workbench - the developer tool for InterBase & Firebird
Upscene Productions

[Non-text portions of this message have been removed]