Oracle 11g – Keep Default Value as 0.0 for 0 or NULL for Numeric Column

nulloracleoracle-11g

I have a numeric column Number(28,3) in which I have to insert value as 0.0 ,
whenever I encounter 0 or NULL/blank at the time of data insertion.

I have tried below :

NVL(col_name,0.0)

but it's just replacing null values with 0

Best Answer

This is just a matter of displaying the data as required:

SQL> create table t1(c1 number(28,3));

Table created.

SQL> insert into t1 values(nvl(null, 0.0));

1 row created.

SQL> select * from t1;

        C1
----------
         0

SQL> col c1 format 999990.0
SQL> select * from t1;

       C1
---------
      0.0

Related Solutions

Sql-server – Update “NULL” string to Actual NULL value

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ' + QUOTENAME(name) + ' = CASE
  WHEN ' + QUOTENAME(name) + ' = ''NULL'' THEN NULL ELSE '
  + QUOTENAME(name) + ' END,'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.YourTableName')
AND system_type_id IN (35,99,167,175,231,239);

SELECT @sql = N'UPDATE dbo.YourTableName SET ' + LEFT(@sql, LEN(@sql)-1) + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

Oracle ORA-12899 Error – How to Resolve When Creating Varchar Virtual Column Based on Numeric Column

The error message is somewhat strange as presented, but when you get this message creating a virtual column, the value for maximum is the minimum length that you need to specify to be able to create the column.

The reason for this is that Oracle doesn't know the maximum length of the expression to_char(a) and assumes that it will be 40 characters: 38 places of precision for the number, plus 1 for each of the decimal point and positive/negative indicator.

Add 2 characters for "A:" and the minimum length that Oracle judges as "safe" is 42.

Best Answer

Related Solutions

Sql-server – Update “NULL” string to Actual NULL value

Oracle ORA-12899 Error – How to Resolve When Creating Varchar Virtual Column Based on Numeric Column

Related Question