DSXchange

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.

will this work for you ?

rameshrr3 wrote: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

How about:

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

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.

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,

jwiles wrote: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.

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.

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?

dr.murthy wrote: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

maybe worth trying with

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


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

maybe worth trying with

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


sometimes it attaches a extra space at the start

you can also use
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.