Page 1 of 1
Creating a left buffer on a column
Posted: Fri Jun 30, 2006 9:34 am
by 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
Posted: Fri Jun 30, 2006 9:38 am
by DSguru2B
Try this
Code: Select all
FMT(DIGITS(DSLink3.CURNT_BAL_AMT),"17'0'R")
Posted: Fri Jun 30, 2006 9:58 am
by 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.
Posted: Fri Jun 30, 2006 10:12 am
by DSguru2B
You mean something like this
Code: Select all
FMT(DSLink3.CURNT_BAL_AMT,"17'0'R0")
Posted: Fri Jun 30, 2006 10:16 am
by 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...
Re: Creating a left buffer on a column
Posted: Fri Jun 30, 2006 10:26 am
by DSguru2B
jshurak wrote: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.
Posted: Fri Jun 30, 2006 10:43 am
by 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.
Posted: Fri Jun 30, 2006 10:53 am
by DSguru2B
I have to say, pretty impressive from memory.
I guess this conversation turned for the best for the OP.
Regards,
Posted: Fri Jun 30, 2006 12:58 pm
by jshurak
I appreciate all the help, but none of these seem to work.
Posted: Fri Jun 30, 2006 1:11 pm
by 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.
Posted: Fri Jun 30, 2006 1:28 pm
by 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?
Posted: Fri Jun 30, 2006 1:34 pm
by 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.
Posted: Fri Jun 30, 2006 6:28 pm
by 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.
Posted: Sat Jul 01, 2006 9:55 am
by 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.
Posted: Sat Jul 01, 2006 1:06 pm
by 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.