Transformer
Moderators: chulett, rschirm, roy
Transformer
Hi-
I have to read data from Oracle table and create to a fixed length text file.
It works fine except a field which has decimal field.
I used a transmformer to convert from decimal to varchar using function decimal to string. The length of the decimal field should (12,2).
Required output:
000000000.00
-00000025.98
-00000682.10
But my output is coming like below.
000000000.00
-000000025.9
-000000682.1
I am looking for the most significat digit dropped and replaced with a negative.
Appreciate your quick response.
Thanks
I have to read data from Oracle table and create to a fixed length text file.
It works fine except a field which has decimal field.
I used a transmformer to convert from decimal to varchar using function decimal to string. The length of the decimal field should (12,2).
Required output:
000000000.00
-00000025.98
-00000682.10
But my output is coming like below.
000000000.00
-000000025.9
-000000682.1
I am looking for the most significat digit dropped and replaced with a negative.
Appreciate your quick response.
Thanks
Last edited by laxmi_etl on Sun Jan 28, 2007 2:27 pm, edited 2 times in total.
Re: Transformer
You could oracle to_char in your source query, to do the same. It would be more efficient too.laxmi_etl wrote:I used a transmformer to convert from decimal to varchar using function decimal to string. The length of the decimal field should (12,2).
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
I am looking for the most significat digit dropped and replaced with a negative.laxmi_etl wrote:The length of that field should be 12,2.DSguru2B wrote:What is the length that you are specifiying for that field? Also make it char and not varchar.
And i tried with char but still it's giving the same output.
.laxmi_etl wrote:I am looking for the most significat digit dropped and replaced with a negative.laxmi_etl wrote:The length of that field should be 12,2.DSguru2B wrote:What is the length that you are specifiying for that field? Also make it char and not varchar.
And i tried with char but still it's giving the same output.
I think I am failed to explain you clearly.Let me give you the requirement
properly.
I am looking for the below format.
000000000.00
-00000025.98
-00000682.10
I am looking for the most significat digit dropped and replaced with a negative.
But when I tried with varcha or char I am getting the same format like below.
000000000.00
-00000025.9
-00000682.1
narasimha wrote:How does your input data look like, can you give a sample of the same?
A B C D
-----------------------------------------------------------------
20040327 007000594628 001 0
20040403 002200674670 001 0
20040410 007600451562 001 1843.5
20040417 005600236480 001 -4.57
20040424 007301178585 001 0
20040501 007600257998 001 0
20040508 007401027936 001 0
20040522 005400806167 001 -12.98
A=VARCHAR2(8)
B=VARCHAR2(12)
C=VARCHAR2(8)
D=NUMBER(12,2) MY ISSUE IS WITH THIS FIELD
The required letngths for the above fields should be these
A=VARCHAR2(8)
B=VARCHAR2(20)
C=VARCHAR2(20)
D=NUMBER(12,2)
Code: Select all
Input: -12.98
select to_char(-12.98, '00000000.00') from dual
Output: -00000012.98
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Also not reported is the method you are using to convert the decimal numbers to strings. Are you using a Modify stage or a Transformer stage? Which function are you using? Are you qualifying that function (for example with "fix_zero" for DecimalToString() function)?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I am using Transformer stage for the conversion of decimal field to varchar.ray.wurlod wrote:Also not reported is the method you are using to convert the decimal numbers to strings. Are you using a Modify stage or a Transformer stage? Which function are you using? Are you qualifying that function (for example with "fix_zero" for DecimalToString() function)?
Using trasformer stage selecting DecimatoString(field_name).
I am not qualifying DecimaltoString function with fix_zero.
While looking in datastage help I have seen that fix_zero but I have no idea of using that.Where do I need to use it.
Thanks
This query works when i use it directly.But I don't know how to use it innarasimha wrote:HTHCode: Select all
Input: -12.98 select to_char(-12.98, '00000000.00') from dual Output: -00000012.98
Datastage.
Thanks
Any thoughts?laxmi_etl wrote:This query works when i use it directly.But I don't know how to use it innarasimha wrote:HTHCode: Select all
Input: -12.98 select to_char(-12.98, '00000000.00') from dual Output: -00000012.98
Datastage.
Thanks
Use it in your source query.laxmi_etl wrote:This query works when i use it directly.But I don't know how to use it innarasimha wrote:HTHCode: Select all
Input: -12.98 select to_char(-12.98, '00000000.00') from dual Output: -00000012.98
Datastage.
Thanks
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.