Issue with DecimalToDecimal

[sumesh.abraham]

I need to calculate the value for a nullable column by doing some division and the derivation uses stage variables. The target column in the oracle table is of
Varchar(25) though. The input for calculation is read from a fixed width file.

If stgVar1 = 1 Then DecimalToDecimal(inputcol[1,15] / 10000000000.0) Else If stgvar1= 2 Then DecimalToDecimal(inputcol[1,15] / 10000.0)
Else If stgvar1 = 3 Then DecimalToDecimal(inputcol[1,15] / 10) Else ""

The derivation for stage variable does not result in a null value.
Since the data type of the output column in target table is Varchar(25), how will I deal with this.

I tried to define the output column as Decimal(8,5) and did the above derivation. In that case, all records are displayed with value as 000.00000.
I tried to do a DecimalToString on top of DecimalToDecimal and made the output column as varchar 25. In that case, null value id written to the table.

When I tried DecimalToDecimal(inputcol[28,15] / 10000000000.0) and tried to write to a sequential file with column definition as Decimal(8,5), value is getting calculated correctly and loaded correctly.`

What could be the issue here? Please let me know your inputs.

[sumesh.abraham]

I had to make some changes in the code to make sure that derivation for stage variable does not result in a null value.

I see a problem now. When I compare the data loaded in the table by the server job (via division logic in the server routine called in BASIC transformer stage) and the data loaded by the parallel job (via division logic implemented in the parallel transformer), I see that for some records the parallel job writes the value rounded to 3 decimal points whereas it is rounded to 4 decimal points in server job. The data type of output column in both server and parallel job is varchar2(25).
Is there a difference in the way the division is done / results are generated in BASIC ransformer and parallel transformer stages.

I am unable to use a DecimalToDecimal as the input value is a string value read from the file.

Please let me know your thoughtts. Thank you!

[ShaneMuir]

Decimal to Decimal is usually used to change one decimal value to another. And generally you would provide the decimal output you wish. (ie DecimaltoDecimal(Value, Format). Also, you seem to be reading in a string and converting it to decimal.

Personally - from what I can gather from your post - I would do the following:
1. Read the first 15 characters of the input col to a stage variable. Set the stage variable as decimal. Datastage should do an implicit conversion from string to decimal. If its a fixed width file - this value cannot be null, but you probably want to do some checks to see if its a valid value.

2. Perform your calculations.

3. Output your values. If your output is varchar - then again datastage should do the implicit conversion. However if you want to change the decimal precision you should probably do so beforehand.

As for your other question - i don't think that the different versions perform divisions any different. I would have thought that the output (if you haven't specified a precision) would merely fill the entire field and move the decimal accordingly. Ie so if your field was 6 characters, and the calculation was 1000000/300 then the output would be 3333.33, whereas if you had 1000000/3000 then the output would be 333.333. But that is only if you didn't provide a precision. If that was provided at eg [6,2] then the outputs would be 3333.33 and 0333.33.

Or at least I think that is how it works.