Trunc Decimals in Division

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
robinlg33
Premium Member
Premium Member
Posts: 13
Joined: Mon Nov 26, 2012 10:19 am
Location: KY

Trunc Decimals in Division

Post by robinlg33 »

I need my result of a division to ROUND DOWN not ROUND UP. In dividing 2 fields I need to truncate the decimal place to 7 positions to Round Down no Round Up. I have tried adding the Environment Variable $APT_DECIMAL_INTERM_SCALE for Integer Type and defaulting the scale to 7 and then doing the following calculation, but it doesn't work. I have looked thru the forum and if I understand correctly this should have truncated to 7 position without rounding. Is that correct and if so it doesn't work. Can you provide any guidance as to what I may be doing wrong?


DecimalToDecimal(00000285407.48 / 0498047217.84,"TRUNC_ZERO")

result received
00000.0005731

result needed
00000.0005730

Thank You in advance.
Robin
BI-RMA
Premium Member
Premium Member
Posts: 463
Joined: Sun Nov 01, 2009 3:55 pm
Location: Hamburg

Post by BI-RMA »

Use DecimalToDecimal(00000285407.48 / 0498047217.84,"TRUNC_ZERO") and set the scale of the target decimal to 7 without changing other default settings on the environment variables, especially not $APT_DECIMAL_INTERM_SCALE, because setting it to 7 causes DataStage to round before the actual DecimalToDecimal-Conversion takes effect.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
robinlg33
Premium Member
Premium Member
Posts: 13
Joined: Mon Nov 26, 2012 10:19 am
Location: KY

Post by robinlg33 »

Your suggestion worked perfectly. Thanks so much for your help!

Robin
Robin
robinlg33
Premium Member
Premium Member
Posts: 13
Joined: Mon Nov 26, 2012 10:19 am
Location: KY

Post by robinlg33 »

Just a note for file in case anyone including myself refers to this issue. I had to also add the Environment Parameter = '$APT_DECIAMAL_INTERM_ROUND_MODE' in order to use the Trunc_Zero within the stage. I think I tried it without and it didn't work.
Robin
Post Reply