Decimal rounding

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Jay
Participant
Posts: 105
Joined: Tue Nov 11, 2003 8:28 pm

Decimal rounding

Post by Jay »

Hi All

This is my problem.

Source is Decimal(18,6). Target is Decimal(7,3).

So if input is 000000002323.423567

result should be 2323.424.

Database: Oracle 8.0

DS version: PX 6.0

I am reading input as Varchar and using field() to extract values. I dont know how far i will be successful this way. This does not seem to be a good way of doing this.

Is there any other way to do this stuff.

Thanks in advance
Jay
mpouet
Participant
Posts: 34
Joined: Mon Oct 18, 2004 10:23 am
Location: France

Re: Decimal rounding

Post by mpouet »

Hi,

I found something for you.
Try this : DecimalToDecimal(StringToDecimal(convert(".", "", Link.FIELD))/1000, 'round_inf')/1000

000000000002.423567 -> 000000000002.424
000000000001.423467 -> 000000000002.423

Ciao
Matthieu
mpouet
Participant
Posts: 34
Joined: Mon Oct 18, 2004 10:23 am
Location: France

Re: Decimal rounding

Post by mpouet »

Just a precision.
I don't know why (may be something with the buffer) but in the transformer you use this formula you have to declare the field as decimal(11,3). If you really need to change to decimal(7,3) (not needed for Oracle) add a second transformer.

Bye
Matthieu
Jay
Participant
Posts: 105
Joined: Tue Nov 11, 2003 8:28 pm

Post by Jay »

Thanks Matthew. I'll try it and let you know how it goes.
Jay
Participant
Posts: 105
Joined: Tue Nov 11, 2003 8:28 pm

Post by Jay »

Hi Matthieu,

What is 'round_inf' doing ?

Thanks
Jay
Jay
Participant
Posts: 105
Joined: Tue Nov 11, 2003 8:28 pm

Partly working...thats a good sign

Post by Jay »

Hi Matthieu

The conversion from Decimal(18,6) to decimal(7,3) is working when the input number=1234.123

But gives a 0000.000 when input number=12345.123456

I try to round(number,3) at source. If i view source, it shows as

decimal(38,10), i.e. 0000000000000000000000012345.1234560000

Any idea as to why its happening ?

PX 6.0 is the version of Datastage. Database is Oracle 8.0.

Thanks
Jay
Jay
Participant
Posts: 105
Joined: Tue Nov 11, 2003 8:28 pm

Post by Jay »

Matthieu,

that is exactly the problem i am facing....

how do i fit in a decimal(8,3) in a column declared decimal(7,3) ?

to circumvent this problem,

i read the input as Varchar(8) to_char(round(input,3))

Now the problem is how to convert this string value to decimal ?

In another thread Ray mentioned some scenarios...i will let u know on Monday...

bye
jay
souravpushp
Participant
Posts: 1
Joined: Mon Oct 30, 2017 8:04 am

Help

Post by souravpushp »

May I know the format of using round_inf ?
Sourav
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

The format? Not knowing exactly what you need let's go with...

As noted earlier, it's one of several optional "rounding types" you can specify during functions that support rounding. The Type Conversion functions are here, scroll down to DecimaltoDecimal for the example discussed in this thread. And from the documentation:

Code: Select all

DecimalToDecimal(mylink.mydec,"round_inf")
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply