Strip zeros

[deva]

Hi,
I have one decimal column wihc is nullable deciaml(9,2). My source is flat file and this column is comming as null.

So I am reading this column as varchar and in the next transformer I am doing the StringToDecimal(string) and loading into table.

But in the table it is loading as 0 (zero). I want to load it as null because of I am getting source as null.

table data type is decimal(9,2).

Please help me on that.

[rameshrr3]

will this work for you ?

Code: Select all

If IsNull(string) Then SetNull() Else StringToDecimal(string)

[deva]

will this work for you ?

Code: Select all

If IsNull(string) Then SetNull() Else StringToDecimal(string)

This is not working Still I am getting 0 into table

[rjdickson]

How about:

Code: Select all

If IsNull(string) or Len(Trim(string))=0 Then SetNull() Else StringToDecimal(string)

[Kryt0n]

Have you got the field in DataStage set as not nullable at any point in the flow?

[ankursaxena.2003]

Put a transformer and do Ramesh's logic as shown below:

If IsNull(string) Then SetNull() Else StringToDecimal(string)

After transformer put a Copy Stage and write into Target Table and also write in Sequential file.
And check in Sequential file if you get Null.

[jwiles]

Is the string coming in as null, "NULL" or is it an empty string (which is NOT the same as null!)? I expect that rjdickson's logic will work for you as it is probably an empty string and not a true null.

Regards,

[vamsi.4a6]

Is the string coming in as null, "NULL" or is it an empty string (which is NOT the same as null!)? I expect that rjdickson's logic will work for you as it is probably an empty string and not a true null.

Regards,

Could you please clarify the below point.From your post i understood null, "NULL" and empty string are different in Datastage

1)NULL fileds can be filtered by using IsNULL function

2)empty string fileds can be filtered by using Len(Trim(string))=0

3)How to filter the null fields in Datastage?


Please correct me if i am wrong for the above three points.

[dr.murthy]

Yes , you are correct NULL fileds will be filtered with ISNULL function and blank strings are filtered with Len(trim(string)) = 0.
But your source is an flat file so you cannot see the null values in a flat file. to filter the null values in a flat file you should check Len(trim(string)) = 0.

[vamsi.4a6]

Thanks for the Reply

1)May i know what is the difference between null and NULL?

@dr.murthy

You mentioned since source is a flat file so you cannot see the null values in a flat file.May i know when null values occur in the source?Is it source dependent?

[Kryt0n]

Yes , you are correct NULL fileds will be filtered with ISNULL function and blank strings are filtered with Len(trim(string)) = 0.
But your source is an flat file so you cannot see the null values in a flat file. to filter the null values in a flat file you should check Len(trim(string)) = 0.

Not totally true, if a null field is defined as "" then any empty string in a sequential file will be treated as null

1)May i know what is the difference between null and NULL?

What you missed is that he stated "NULL" as in the literal value "NULL" not the null value

[sajal.jain]

maybe worth trying with

If IsNull(string) Then SetNull() Else StringToDecimal(trimLeadingTrailing(string))


sometimes it attaches a extra space at the start[/b]

[sajal.jain]

maybe worth trying with

If IsNull(string) Then SetNull() Else StringToDecimal(trimLeadingTrailing(string))


sometimes it attaches a extra space at the start

[rameshrr3]

you can also use

Code: Select all

If Trim(NullToEmpty(string)) ='' Then SetNull() Else 
StringToDecimal(Trim(String))

and I guess it works better . I did not account for the possibility of empty data/spaces in my original derivation.

Does your string have Leading Zeroes ? That needs to be handled seperately.