DSXchange

Hi,

I have job design like this. 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.

Why is it doing like this.

Appreciate your help.

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.

Datastage guru's needs to confirm this.

Please give examples of values into and out of each Transformer stage, indicating whether each example is as expected.

ray.wurlod wrote:Please give examples of values into and out of each Transformer stage, indicating whether each example is as expected.

Here is the data


Data in source table:

col1: Null
Col2: 0
col3: 20


After Transformation 1:

Col1: 000000000000.00
Col2: 000000000000.00
col3: 000000000020.00


After Transformation 2:

output I am getting: 000000000020.00


Output Needed: 000000000000.00 ( As col2 is 0 and not a null value)

What are the datatypes and default values ?

Did you post the derivation copied from the transformer or was it manually typed ? It will be better to see the actual derivation.

Why don't you do something like

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.

Sainath.Srinivasan wrote:What are the datatypes and default values ?

Did you post the derivation copied from the transformer or was it manually typed ? It will be better to see the actual derivation.

Why don't you do something like

Code: Select all

NullToValue(col1, NullToValue(col2, NullToZero(col3)))

For all columns source and target datatypes are decimal(14,2) and there are no default values.

I verified the derivation i posted is exactly same as in my transformer with column name changed.

Would appreciate if you could let me know whats going wrong

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).

All my source and target fields are decimal(14,2)

Note: There are no default values.

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.

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.

I got this idea, when I saw that, you are passing blank values '' to the Decimal feild.

I'm not very sure about this, but maybe if you use @NULL instead of '' that might solve the problem.

'' 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:

Code: Select all

If  isnotnull(col1)
      Then col1 
      Else If isnotnull(col2) 
      Then col2 
      Else if col3<>0 
      Then col3 
      Else 
      0 

Thanks for your reply.
This will work but the thing is i cannot remove first transformer since it is being used downstream by many other stages.

I think the only other option will be to do decimaltostring for columns so that it will hold ''(empty).
Do you think this will be the write approach??

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.