how to replace a data 0 to null
701993May 18 2009 — edited May 18 2009In a TABLE, I have a field with data type NUMBER.
I am creating a VIEW using the TABLE.field and in that view Im trying to replace the TABLE.field data. That is if data is o it should be replace with NULL.
Eg:
CREATE VIEW AS (
SELECT replace (TABLE.field, 0, null) AS TABLE.field,
..............
FROM TABLE;
Here I face problem in the below cases.
CASE1: if value is 0, it is replaced with NULL.
THIS iS EXPECTED.
CASE2: if value is 10.5, it is replaced with 1.5 which is NOT EXCEPTED.
My requirement is only to change the value zero to NULL.
Also the field datatype in the view is changed to VARCHAR, which is different from table.field datatype.
So how to replace only the value zero to NULL?