ODBC --> Tranformer 1 --- > Transformer2 -> ODBC
Transformer1 : if isnull(col1) then '' else col1
if isnull(col2) then '' else col2
if isnull(col3) then 0 else col3
Transformer 2: If col1<>''
Then col1
Else If col2<>''
Then col2
Else if col3<>0
Then col3
Else
0
col1, col2, col3 are decimal(14,2)
The problem i am having is when the col1 and col2 are "0" (not null) then also i am getting col3 in output.
I am assuming when you are loading empty '' for the col1 and col2 in your 1st transformer. Since the col1 and col2 data type is decimal(14,2) it is converting empty'' to "0".
And in the next stage when you are comparing col1 and col2 for <> '' (empty) it is internally doing col1 and col2 <> 0.
Hence for any nulls and 0's in your input col1 and col2 you are getting output as col3.
I am not really sure if datastage will substitute 0 for ''(empty) for comparison during the 2nd transformer calcualtion.
How come your Col1 after Transformation 1 is not an empty string but 0? Looks to me that is there is some inconsistency between the logic and examples you have provided.
kris007 wrote:How come your Col1 after Transformation 1 is not an empty string but 0? Looks to me that is there is some inconsistency between the logic and examples you have provided.
I verified the data and derivations and i provided they are in align with what i have.
Then col1 Null is not becoming empty but 0, may be this is due to having the field as decimal (14,2).
Looks like you are converting from Varchar to Decimal.
Do a PEEK from the first transformer to see the values thats been passing to the next.
BTW, why do you need to two transformer for this purpose.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
kumar_s wrote:Looks like you are converting from Varchar to Decimal.
Do a PEEK from the first transformer to see the values thats been passing to the next.
BTW, why do you need to two transformer for this purpose.
I have only decimal(14,2). NO where i am using varchar data type for this fields.
'' is empty string, the most important word there being string. If you set a decimal 14,2 to empty string the result will be 000000000000.00 instead of empty string.
You cannot compare a decimal to a string (first two comparisons of the second transformer), its not a valid comparison which is why your second transformer is giving you the value for col3, this comparison is a numeric (0) and is true.
Eliminate your first transformer and just use this:
kwwilliams wrote:'' is empty string, the most important word there being string. If you set a decimal 14,2 to empty string the result will be 000000000000.00 instead of empty string.
You cannot compare a decimal to a string (first two comparisons of the second transformer), its not a valid comparison which is why your second transformer is giving you the value for col3, this comparison is a numeric (0) and is true.
Eliminate your first transformer and just use this:
No I don't. You would also have to change the data type to char to hold a string. Once you do this, you have to change all of your downstream stages to work with strings instead of decimal.
Also, If everything downstream from transformer 1 is expecting empty string for null values, then the results of this job are highly suspect and have always been highly suspect. Becuase they have always received 000000000000.00 instead.
I think you need to step back and look at the requirements that this job is attempting to satisfy, and modify the job so it will produce the correct result. No one here can tell you what is right or wrong from a design perspective without understanding the business requirement for your job. All I can say is technically this job will not produce an accurate result and putting a decimaltostring function around the value will not work without changing the data type and all downstream data types.
