Performing right() fn with "+" addition

venkycool · Post by **venkycool** » Tue Jan 03, 2012 12:35 pm

I am deriving an aggregation column which is a_pay (decimal 11, 2) and writing to a transformer apay (char 12) with the below derivation.

"+":Right("00000000000" : DecimalToString(NullToZero(a_pay) * 100,"suppress_zero") , 11)

Somehow, I am getting "+.0000000001" to the output instead "+0000000001". Why do I get "." after the +. Any suggestions.

I have the similar derivation for the rest of 5 columns, 2 columns behave like this and others seems to be fine.

Any suggestions? inputs? Please let me know for further questions.

Thanks in Advance

ray.wurlod · Post by **ray.wurlod** » Tue Jan 03, 2012 2:52 pm

What's the actual value in a_pay (or similar) for the columns that work and the columns that don't work?

venkycool · Post by **venkycool** » Tue Jan 03, 2012 2:58 pm

ray.wurlod wrote:What's the actual value in a_pay (or similar) for the columns that work and the columns that don't work? ...

a_pay has +000150000 and I am mapping a_pay to apay with the above said derivation

Note: a_pay is different data now since I mock up test bills. Still there should not be +.00000150000

jwiles · Post by **jwiles** » Tue Jan 03, 2012 3:33 pm

Are you certain of the value of a_pay? What is the result of just DecimalToString(a_pay) without any of the other logic?

venkycool · Post by **venkycool** » Wed Jan 04, 2012 1:34 pm

jwiles wrote:Are you certain of the value of a_pay? What is the result of just DecimalToString(a_pay) without any of the other logic?

Yes the value is certain. I didnt try just DecimalToString. Is there any possibility with the logic I could remove the "." if it comes?

pandeesh · Post by **pandeesh** » Wed Jan 04, 2012 1:48 pm

If it's varchar, then use Convert() for removing the '.'.

venkycool · Post by **venkycool** » Wed Jan 04, 2012 1:51 pm

pandeesh wrote:If it's varchar, then use Convert() for removing the '.'.

It's Char. But can try varchar. Any other suggestions?

pandeesh · Post by **pandeesh** » Wed Jan 04, 2012 1:53 pm

Please try

Code: Select all

Convert(".","",VALUE)

and let us know.

dsusersaj · Post by **dsusersaj** » Wed Jan 04, 2012 1:57 pm

I am not sure how you managed to get that '.'.

Becasue I just tried the same logic that you gave in the inital question.

My job design is Col generator (value of col1(deimal 11,2)=15000000
Then Transformer with the exact derivation
"+":Right("00000000000" : DecimalToString(NullToZero(DSLink2.col1) * 100,"suppress_zero") , 11). The output column is a char(12).

I output the result to peek. The peek dispayed

"Peek_5,0: outputcol:+00015000000"

I am running it in datastage 7.5.3 on UNIX

venkycool · Post by **venkycool** » Wed Jan 04, 2012 2:30 pm

pandeesh wrote:Please try
Code: Select all
Convert(".","",VALUE)
and let us know.

It's not workng out! I am still getting "."

venkycool · Post by **venkycool** » Wed Jan 04, 2012 2:31 pm

pandeesh wrote:Please try
Code: Select all
Convert(".","",VALUE)
and let us know.

It's not workng out! I am still getting "."

qt_ky · Post by **qt_ky** » Fri Jan 06, 2012 10:31 am

Ray had asked for values of columns that don't work AND columns that do work as expected.

What are the data types of the columns the don't work and columns that do work? Any differences?

What warnings are you getting in the job log?

Did you say your using an Aggregator stage before a Transformer? If so, what is it doing?

venkycool · Post by **venkycool** » Tue Jan 10, 2012 2:16 pm

Sorry to get back late...

Actually, found a problem here..Everything works great in Dev env but The same job is not working in QA as it gives me the output with decimal with wrong data.

Any suggestions here...It is the same Oracle database for both dev and qa.

Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Jan 10, 2012 3:28 pm

What does "not working" mean?

What's different between DEV and QA? (Hint: "nothing" is not the correct answer.)

venkycool · Post by **venkycool** » Tue Jan 10, 2012 3:52 pm

I don't say nothing but can this happen?
Ray, we have our jobs which are 4 node configuration and jobs itself have some partition and re-partition within stages like Aggregator ( to sum calculations). When I run a sequence in DataStage through unix, I will have an ouput to verify the summation of records for particular record type. Now, I do see strange things in the records appearing as +.0000001 instead of correct records ie., +0000756 when I run in QA. Similarly I run the same sequence in in Dev unix box, I see the expected output as +0000756. We have the Oracle database as sourced. Both dev and qa are referred to the same database.

I have no clue why this is misbehaving. I tried re-importing still having issues in qa