Creating a left buffer on a column

[jshurak]

A couple of decimal columns we have are appearing as so: 285.50.

we need them to appear like this: 00000000000028550

we have put to following into the transformer stage with no luck

str("0",17-len(convert(".","",DSLink3.CURNT_BAL_AMT)))

For some reason this is still producing the original output.

Any help would be great

[DSguru2B]

Try this

Code: Select all

FMT(DIGITS(DSLink3.CURNT_BAL_AMT),"17'0'R")

[chulett]

While the DIGITS transform will remove the 'dot' there is an official way to do that using the FMT command all at the same time. Check into the 'descaling' option.

[DSguru2B]

You mean something like this

Code: Select all

FMT(DSLink3.CURNT_BAL_AMT,"17'0'R0")

[chulett]

From memory, more like:

Code: Select all

FMT(DSLink3.CURNT_BAL_AMT,"17'0'R2")

To ask it to move the decimal point over two places...

[DSguru2B]

we need them to appear like this: 00000000000028550

The OP doesnt want the decimal point anymore. He just wants to strip it out and fill in leading zeros to honor length of 17. Thats why I put in a R0 and not R2.

[chulett]

Oh, trust me - I know what they want it to look like... and I said it was from memory. Simple enough to build a little test harness to check.

Turns out we need to combine our answers:

Code: Select all

FMT(DSLink3.CURNT_BAL_AMT,"17'0'R02")

From the help:

n[m]
n is a number, 0 through 9, that specifies the number of decimal places to display. If you specify 0 for n, the value is rounded to the nearest integer. The output is padded with zeros or rounded to the nth decimal place, if required.

m specifies how to descale the value:

A value of 0 descales the value by the current precision.

A value of 1 through 9 descales the value by m minus the current precision.

If you do not specify m, the default value is 0. The default precision is 4.

[DSguru2B]

I have to say, pretty impressive from memory.
I guess this conversation turned for the best for the OP.
Regards,

[jshurak]

I appreciate all the help, but none of these seem to work.

[kcbland]

You need to multiply by 100, then pad with zeroes. Padding is simple.

FMT(link.column*100, "R%17")

Make sure your metadata for the column is VARCHAR, if it's still DECIMAL with a scale factor the job will strip off the zeroes again.

[DSguru2B]

This is really strange. HOw come it doesnt work? Try Ken's suggestion, that will most certainly work. Did you try loading the output to a sequential file instead of the table. What is the sql type, length of your target column?

[jshurak]

we are extracting from our Oracle DB. The output does get loaded into a sequential file. Ken's suggestion worked, I did have to change the metadata from decimal to varchar. The original extract was decimal 17.

[chulett]

You don't "have to" multiply by 100, although you certainly can. I know what I posted for the FMT command works as I tested it with your input and got your desired output. It's not like we've never helped people do this before.

Of course you need to define the output as some sort of character field when you do zero filling or padding like this, a decimal / numeric field will supress silly things like leading zeroes.

[DSguru2B]

Thats exactly what i was wondering too. I tested out my code too. I knew we were missing some crucial pieces of the info.

[kcbland]

The missing piece was that DS Server arbitrarily enforces the data type in the metadata. Sequential file stage will apply decimal and scale metadata, so when changing the data to be character based you must remember to change the metadata.