Null Handling

Prakash3366 · Post by **Prakash3366** » Fri Jul 24, 2009 12:13 pm

Hi All

i have salary column , but few fields are null. If salary is null then i want 999 in the target otherwise i want the salary itself.

Ex:
Input:

ID|NAME|SALARY
101,RAY,10000
102,CHIRS,
103,RAM,20000
104,JESSY,

Output i want as :

ID|NAME|SALARY
101,RAY,10000
102,CHIRS,999
103,RAM,20000
104,JESSY,999

Thanks in advance.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jul 24, 2009 12:45 pm

search for isNull or NullToZero

ddevdutt · Post by **ddevdutt** » Fri Jul 24, 2009 2:01 pm

NullToValue should get you your output

chulett · Post by **chulett** » Fri Jul 24, 2009 2:45 pm

You'd have to create a NullToValue transform as it does not ship with the Server product, but it is quite easy to write. Or do the equivalent IF-THEN-ELSE check directly in the derivation using IsNull() as noted.

Prakash3366 · Post by **Prakash3366** » Sat Jul 25, 2009 11:37 am

Thanks for everybody. I know NullToValue in parallel but in server if i use IF THEN ELSE , I am not getting the desired value.. i am getting only null values in place of null values in the target. so please reply..

Thanks

chulett · Post by **chulett** » Sat Jul 25, 2009 1:37 pm

Post your syntax.

Prakash3366 · Post by **Prakash3366** » Mon Jul 27, 2009 1:00 pm

chulett,
Thanks for your reply. i am putting the following syntax

If column=isNull then 999 else '0'

Please correct if i am wrong

Thanks
Prakash

chulett · Post by **chulett** » Mon Jul 27, 2009 1:37 pm

IsNull() is a function and you said you wanted to retain the value if not null so:

If IsNull(column) then 999 else column

Prakash3366 · Post by **Prakash3366** » Tue Jul 28, 2009 10:48 am

Chulett,
Thanks for your quick reply and i have mentioned as you are said , but still i am not getting solution for this.

If IsNull(a.SALARY) then 999 else a.SALARY.
Here is the a is the source link.

if i put this condition , i am not getting 999 in the place of null, i am getting only null in the null place.
So please reply for the same..

Thanks

chulett · Post by **chulett** » Tue Jul 28, 2009 10:50 am

Then your Salary field is not null. You may need to add a check for that field being "empty" as well. What data type is it?

Prakash3366 · Post by **Prakash3366** » Wed Jul 29, 2009 12:34 pm

chulett,

I want to explain the job.

my source has three columns. ID, Name and salary.
the datatype of these columns is integer, varchar and integer respectively.
i checked option, Nullable is Yes for salary column. But still i am not getting desired result.
can you help me about this.

Thanks

ddevdutt · Post by **ddevdutt** » Wed Jul 29, 2009 2:59 pm

Like Craig mentioned, you probably have values that are not null in the Salary field. Hence apart from checking for nulls, also check to see if those fields are empty.

Prakash3366 · Post by **Prakash3366** » Wed Jul 29, 2009 11:57 pm

Hi

in salary column, i have 10 rows . In that two ,three fields are empty values. But still i am not getting the correct result. So now you can help me about this

Thanks

chulett · Post by **chulett** » Thu Jul 30, 2009 12:11 am

What is your source for this column - flat file? Database table? Something else entirely? Assuming the first but looking for confirmation.

Prakash3366 · Post by **Prakash3366** » Thu Jul 30, 2009 5:09 am

My Source is flat file

Thanks