Transformer

[laxmi_etl]

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

[DSguru2B]

What is the length that you are specifiying for that field? Also make it char and not varchar.

[narasimha]

I used a transmformer to convert from decimal to varchar using function decimal to string. The length of the decimal field should (12,2).

You could oracle to_char in your source query, to do the same. It would be more efficient too.

[laxmi_etl]

What is the length that you are specifiying for that field? Also make it char and not varchar.

The length of that field should be 12,2.
And i tried with char but still it's giving the same output.

[laxmi_etl]

What is the length that you are specifiying for that field? Also make it char and not varchar.

The length of that field should be 12,2.
And i tried with char but still it's giving the same output.

I am looking for the most significat digit dropped and replaced with a negative.

[laxmi_etl]

What is the length that you are specifiying for that field? Also make it char and not varchar.

The length of that field should be 12,2.
And i tried with char but still it's giving the same output.

I am looking for the most significat digit dropped and replaced with a negative.

.

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]

How does your input data look like, can you give a sample of the same?

[laxmi_etl]

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)

[narasimha]

Code: Select all

Input: -12.98 
select to_char(-12.98, '00000000.00')  from dual
Output: -00000012.98

HTH

[ray.wurlod]

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)?

[laxmi_etl]

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)?

I am using Transformer stage for the conversion of decimal field to varchar.

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

[laxmi_etl]

Code: Select all

Input: -12.98 
select to_char(-12.98, '00000000.00')  from dual
Output: -00000012.98

HTH

This query works when i use it directly.But I don't know how to use it in
Datastage.

Thanks

[laxmi_etl]

Code: Select all

Input: -12.98 
select to_char(-12.98, '00000000.00')  from dual
Output: -00000012.98

HTH

This query works when i use it directly.But I don't know how to use it in
Datastage.

Thanks

Any thoughts?

[narasimha]

Code: Select all

Input: -12.98 
select to_char(-12.98, '00000000.00')  from dual
Output: -00000012.98

HTH

This query works when i use it directly.But I don't know how to use it in
Datastage.

Thanks

Use it in your source query.