StringToDecimal - behavior with direct Arith ops

[Kirtikumar]

Hi,

I am trying following:
I am getting a column TRAN_AMT. This amount am reading as string from file.
The amount is spaces sometimes which indicates it is 0.

Now I have to generate three columns AmtPls8(as TRAN_AMT + and AmtMult8 (as TRAN_AMT * .

If while deriving above columns, I directly use the derivations as:

Code: Select all

AmtPls8 = StringToDecimal(TRAN_AMT) + 8
AmtMult8 = StringToDecimal(TRAN_AMT) * 8

With input as spaces, I am getting the output for the cols as 000123456754.12 and 000987653968.99 respectively whereas actual output should be 8 and 0.

Now I tried a bit diff approach to it. I added one stage variable AmtSVar and code is changed as follows:

Code: Select all

Stage variable:
       AmtSVar = StringToDecimal(TRAN_AMT)
Cols Derivation:
       AmtPls8 = AmtSVar + 8
       AmtMult8 = StringToDecimal(TRAN_AMT) * 8

After this the result was 000000000008.00 and 000000000064.00 respectively meaning for first col the result is OK and during the second calc it again behaves strangely.

Then I change the cols to have derivations as:

Code: Select all

AmtPls8 = AmtSVar + 8
AmtMult8 = AmtSVar * 8

After this am getting proper results.

One more thing if the incoming column value is proper number and not spaces, then everything works fine and no need for the stage variable. But if it spaces, then it needs stage var to make the logic work proper.

[ray.wurlod]

Handle the null explicitly within the expression. Use

Code: Select all

NullToValue(TRAN_AMT,0)

(there may even be a NullToZero function; I'm working from memory here).

[chulett]

There is one in Server, wouldn't be surprised if the equivalent exists in PX.

[rasi]

Ray is correct there is NullToZero function in PX.

Krithik you need to use the Trim function to remove the space and then apply NullToZero this should work.