Format of decimal value in sequential files

[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).

[chulett]

Declare the output field as a string and use the DecimalToString() function with an appropriate output mask.

[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.

[boxtoby]

You could try multiplying the value 100 million, the DecimalToString, then substring off the characters you want.

Bob.

[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.

[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.

[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.