top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

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

+2 votes
417 views
A table contains some null values. How to get (Not Applicable(NA)) in place of that null value in target?
posted Jul 31, 2015 by Sunil

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

3 Answers

+1 vote

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 Jul 31, 2015 by Arif Shaikh
0 votes

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

if is_null(column_name,-NA-,column_name)

answer Jul 31, 2015 by Manikandan J
0 votes

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.

answer Jul 31, 2015 by anonymous
Similar Questions
+1 vote

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

0 votes

I have the following table with the shown data in it:

send_date | household_ID

11-20-2014 | 123
11-20-2014 | 456
11-15-2014 | 789

I need to do 2 things:

1) Calculate the max value for send_date

2) Filter out any rows whose send_date does not match that value

In other words, I want the output to be:

send_date | household_ID

11-20-2014 | 123
11-20-2014 | 456

Row number 3 should be filtered out as its send_date is not the max.

I tried creating an aggregate, grouping by all columns, and creating a new output port called MAX_DATE with an expression of MAX(SEND_DATE), then have a filter transformation with the condition MAX_DATE = SEND_DATE

This lets all rows through, though. What can I do to make this work....

+1 vote

i have taken target table as my lookup table using static cache.in source i have duplicate values.

in mapping i ahve used update strategy transformation but not i m not able update data in target table

Example:Initially(i mean after session Load)

source table Lookup table Target table

ID    Name           ID    Name            ID    Name    
1     A              1     A               1     A 
2     B                                    2     B

Now im inserting two more records

3      C
1      E

but its not updating below record

1    E

i am getting below error

One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "TABLE_NAME" from having duplicate values for the index key.

I know if i use dynamic lookup it will work correctly but in static. please give the reason ASAP.

...