A table contains some null values. How to get (Not Applicable(NA)) in place of that null value in target?

Question

I am using below statement but i got error.
ALTER TABLE XXXtable
ADD xxx_name varchar2(50) not null;

error message:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

Answer 1

Two Options

1) Use IIF condition

for e.g: IIF(ISNULL(COMM),'N/A',COMM)

If your COMM values is coming as NULL it would be replaced by N/A else Value would be written to Target.

2) Double Click on Expression Transformation, Select the port where you are expecting NULL Values. At the bottom there is a tab of Default Value, write 'N/A' and you would get N/A in output if there is NULL value in that column else Value.

Hope it helps, Vote me if the answer is correct.

Arif

Answer 2

USE: decode function in Expression taking one new column as Flag

if is_null(column_name,-NA-,column_name)

Answer 3

1) IIF(ISNULL(COMM),'N/A',COMM)
By similar condition you can replace data in target with N/A where comm is NULL else value would be written.

2) Double Click on Expression Transformation and Click on the port where you are Expecting NULL Values. At the bottom replace Default Value with 'N/A'. That will give you the desired output.