| Subject | Re: [firebird-support] Null to Zero conversion |
|---|---|
| Author | Cavit Aladag |
| Post date | 2003-08-29T06:50:43Z |
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.
Regards,
Cavit Aladag
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.
Regards,
Cavit Aladag
----- Original Message -----
From: Martijn Tonies
To: firebird-support@yahoogroups.com
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
http://www.upscene.com
[Non-text portions of this message have been removed]