Performing right() fn with "+" addition
Moderators: chulett, rschirm, roy
Performing right() fn with "+" addition
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
"+":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
Suriv
No one is perfect... that's why pencils have erasers
No one is perfect... that's why pencils have erasers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
a_pay has +000150000 and I am mapping a_pay to apay with the above said derivationray.wurlod wrote:What's the actual value in a_pay (or similar) for the columns that work and the columns that don't work? ...
Note: a_pay is different data now since I mock up test bills. Still there should not be +.00000150000
Suriv
No one is perfect... that's why pencils have erasers
No one is perfect... that's why pencils have erasers
Yes the value is certain. I didnt try just DecimalToString. Is there any possibility with the logic I could remove the "." if it comes?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?
Suriv
No one is perfect... that's why pencils have erasers
No one is perfect... that's why pencils have erasers
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
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
It's not workng out! I am still getting "."pandeesh wrote:Please tryand let us know.Code: Select all
Convert(".","",VALUE)
Suriv
No one is perfect... that's why pencils have erasers
No one is perfect... that's why pencils have erasers
It's not workng out! I am still getting "."pandeesh wrote:Please tryand let us know.Code: Select all
Convert(".","",VALUE)
Suriv
No one is perfect... that's why pencils have erasers
No one is perfect... that's why pencils have erasers
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?
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?
Choose a job you love, and you will never have to work a day in your life. - Confucius
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
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
Suriv
No one is perfect... that's why pencils have erasers
No one is perfect... that's why pencils have erasers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
Suriv
No one is perfect... that's why pencils have erasers
No one is perfect... that's why pencils have erasers