Creating a left buffer on a column
Moderators: chulett, rschirm, roy
Creating a left buffer on a column
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
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
Try this
Code: Select all
FMT(DIGITS(DSLink3.CURNT_BAL_AMT),"17'0'R")
Last edited by DSguru2B on Fri Jun 30, 2006 10:07 am, edited 1 time in total.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
You mean something like this
Code: Select all
FMT(DSLink3.CURNT_BAL_AMT,"17'0'R0")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
From memory, more like:
To ask it to move the decimal point over two places...
Code: Select all
FMT(DSLink3.CURNT_BAL_AMT,"17'0'R2")
To ask it to move the decimal point over two places...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Creating a left buffer on a column
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.jshurak wrote:we need them to appear like this: 00000000000028550
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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:
From the help:
Turns out we need to combine our answers:
Code: Select all
FMT(DSLink3.CURNT_BAL_AMT,"17'0'R02")
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle