Page 1 of 1

Format of decimal value in sequential files

Posted: Tue Mar 17, 2015 9:37 am
by RobP
Need to develop a job that outputs a decimal value in the format 99999.999, input is a databse column defined as decimal (8,3).
Some requirements:
- Format is fixed length, so all positions need to be padded
- First position not used for +/- sign

Tried to use the decimal format in the format tab of the sequential file stage vut that always give me an empty first character (for the sign I think).

Is there an easy way to do this (or somewhere described how to format numbers).

Posted: Tue Mar 17, 2015 10:55 am
by chulett
Declare the output field as a string and use the DecimalToString() function with an appropriate output mask.

Posted: Wed Mar 18, 2015 3:01 am
by RobP
Thank for your reply, but I was not successfull in doing this. I would have hoped there could be a format mask like "&&&&&&&.&&" or something like that. The only things I could find are suppres_zero and that kind of stuff.

Input is a decimal data fileld with data like:
45.000
45.675
45.300
123.5

Output should be:
000045.000
000045.675
000045.300
000123.500

So no leading space for the sign and padded with zero's left and right.

Posted: Wed Mar 18, 2015 3:11 am
by boxtoby
You could try multiplying the value 100 million, the DecimalToString, then substring off the characters you want.

Bob.

Posted: Wed Mar 18, 2015 7:34 am
by chulett
Well, crap... I thought I remembered that it supported a format mask but it doesn't, silly function. There are Server functions that will do that if you are willing to use a BASIC Transformer. Or still use the DecimalToString function with 'fix_zero' argument and you'll get values like this:

0000000000000000000000000045.0000000000
0000000000000000000000000045.6750000000
0000000000000000000000000045.3000000000
0000000000000000000000000123.5000000000

Then substring out the piece that you want:

TheBigString[23,32]

Pretty similar to what Bob suggested. :wink:

Posted: Wed Mar 18, 2015 9:55 am
by AshishDevassy
if nothing works.

Split the input decimal number into 2 (delimit on decimal point). 1.signficand 2.mantissa

Leftpad the significand and right pad the mantissa with zeroes (length as pr requirement) and then concatenate the two.

Posted: Wed Mar 18, 2015 12:42 pm
by qt_ky
A simple variation on the theme:

Code: Select all

DecimalToString(input_column)[9]
If you read the database column as a decimal(8,3) then it should automatically pad with leading and trailing zeros. If it does not for some reason, then you could define a stage variable as a decimal(8,3) as an intermiadiate step.

The [9] takes the 9 right-most characters from the string.