Strip zeros
Moderators: chulett, rschirm, roy
Strip zeros
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.
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 ?
Code: Select all
If IsNull(string) Then SetNull() Else StringToDecimal(string)
This is not working Still I am getting 0 into tablerameshrr3 wrote:will this work for you ?
Code: Select all
If IsNull(string) Then SetNull() Else StringToDecimal(string)
How about:
Code: Select all
If IsNull(string) or Len(Trim(string))=0 Then SetNull() Else StringToDecimal(string)
Regards,
Robert
Robert
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm
Could you please clarify the below point.From your post i understood null, "NULL" and empty string are different in Datastagejwiles 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,
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.
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.
D.N .MURTHY
Not totally true, if a null field is defined as "" then any empty string in a sequential file will be treated as nulldr.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.
What you missed is that he stated "NULL" as in the literal value "NULL" not the null value1)May i know what is the difference between null and NULL?
-
- Participant
- Posts: 11
- Joined: Sat Mar 13, 2010 12:00 am
-
- Participant
- Posts: 11
- Joined: Sat Mar 13, 2010 12:00 am
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.
Code: Select all
If Trim(NullToEmpty(string)) ='' Then SetNull() Else
StringToDecimal(Trim(String))
Does your string have Leading Zeroes ? That needs to be handled seperately.