stringtodecimal function clarification
Moderators: chulett, rschirm, roy
stringtodecimal function clarification
I have gone through StringToDecimal function in below link but still have below clarifications.
https://www-01.ibm.com/support/knowledg ... tions.html
1)StringToDecimal("19982.22") converted to Decimal(7,2) produces 19982.22
a)what is the default rtype for the above example?
b)No rounding is happened here but in example 2 rounding is happened?
------------------------------
2)StringToDecimal("19982.2276") converted to Decimal(7,2) produces 19982.23
a)what is the default rtype for the above example?
------------------------------
3)StringToDecimal("19982.2276", "ceil") converted to Decimal(7,2) produces 19982.23
a)How to understand the above output whether 19982.22 or 0.22 or which value will be ceiled?
------------------------------
4)StringToDecimal("19982.2276", "trunc_zero") converted to Decimal(7,2) produces 19982.22
a)How to understand the above output
trunc_zero. Discard any fractional digits to the right of the rightmost fractional digit supported in the destination, regardless of sign. is not clear to me
https://www-01.ibm.com/support/knowledg ... tions.html
1)StringToDecimal("19982.22") converted to Decimal(7,2) produces 19982.22
a)what is the default rtype for the above example?
b)No rounding is happened here but in example 2 rounding is happened?
------------------------------
2)StringToDecimal("19982.2276") converted to Decimal(7,2) produces 19982.23
a)what is the default rtype for the above example?
------------------------------
3)StringToDecimal("19982.2276", "ceil") converted to Decimal(7,2) produces 19982.23
a)How to understand the above output whether 19982.22 or 0.22 or which value will be ceiled?
------------------------------
4)StringToDecimal("19982.2276", "trunc_zero") converted to Decimal(7,2) produces 19982.22
a)How to understand the above output
trunc_zero. Discard any fractional digits to the right of the rightmost fractional digit supported in the destination, regardless of sign. is not clear to me
From the documentation:
ceil - means always round the last usable digit UP! So in your example number 3, your question is incorrect. It is only looking at the very last usable digit (the second "2"), and rounding it up because there were some numbers after it.
floor - this is the reverse - always round down.
trunc-zero - no rounding. Chop the number off to fit and ignore anything left over.
round_inf - this is the rounding we were taught in school. For positive numbers, if the first unusable digit is "5" or greater, then round the previous digit up. If it is "4" or lower, then round the previous digit down. This is also the default, so its the answer to example #2 you gave. It is using round_inf.ceil. Round the source field toward positive infinity. For example, 1.4 -> 2, -1.6 -> -1.
floor. Round the source field toward negative infinity. For example, 1.6 -> 1, -1.4 -> -2.
round_inf. Round or truncate the source field toward the nearest representable value, breaking ties by rounding positive values toward positive infinity and negative values toward negative infinity. For example, 1.4 -> 1, 1.5 -> 2, -1.4 -> -1, -1.5 -> -2.
trunc_zero. Discard any fractional digits to the right of the rightmost fractional digit supported in the destination, regardless of sign. For example, if the destination is an integer, all fractional digits are truncated. If the destination is another decimal with a smaller scale, round or truncate to the scale size of the destination decimal. For example, 1.6 -> 1, -1.6 -> -1.
ceil - means always round the last usable digit UP! So in your example number 3, your question is incorrect. It is only looking at the very last usable digit (the second "2"), and rounding it up because there were some numbers after it.
floor - this is the reverse - always round down.
trunc-zero - no rounding. Chop the number off to fit and ignore anything left over.
1)can anybody elaborate on below quote.Do we need to consider entire number or part of a number for ceiling?
2)StringToDecimal("19982.2276", "ceil") converted to Decimal(7,2)
which 2 we need to consider for ceiling
2)StringToDecimal("19982.2276", "ceil") converted to Decimal(7,2)
which 2 we need to consider for ceiling
asorrell wrote:ceil - means always round the last usable digit UP! So in your example number 3, your question is incorrect. It is only looking at the very last usable digit (the second "2"), and rounding it up because there were some numbers after it.
If the precision is 2 digits after the decimal, then the rounding type considers the digits that follow. The rounding type will affect the last digit that you are keeping. Please just take 10 minutes to test it out yourself using a row generator stage.
Choose a job you love, and you will never have to work a day in your life. - Confucius
Thanks a lot for Input. I tried with different values to understand each functionality but not able to understand below things.
Source is one field is varchar(11)
1)Input column, output column
19982.2776, 19982.27
output column type and derivation:(decimal 7,2) and derivation:StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1), "floor")
clarification:I excepted 19982.26 since floor should happen for digit highlighted in bold color
2)Input column, output column
-19982.2876,-19982.287
output column type and derivation:(decimal 7,3) and derivation:StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1), "ceil")
clarification:I excepted -19982.286 since ceil should happen for digit highlighted in bold color
3)Input column, output column
19982.2345, 19982.23
output column type and derivation:(decimal 7,2) and derivation:StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1), "round_inf")
clarification:I excepted 19982.22 since round_inf should happen for digit highlighted in bold color
4)default thing
StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1)) and what is the default rounding type?
Source is one field is varchar(11)
1)Input column, output column
19982.2776, 19982.27
output column type and derivation:(decimal 7,2) and derivation:StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1), "floor")
clarification:I excepted 19982.26 since floor should happen for digit highlighted in bold color
2)Input column, output column
-19982.2876,-19982.287
output column type and derivation:(decimal 7,3) and derivation:StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1), "ceil")
clarification:I excepted -19982.286 since ceil should happen for digit highlighted in bold color
3)Input column, output column
19982.2345, 19982.23
output column type and derivation:(decimal 7,2) and derivation:StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1), "round_inf")
clarification:I excepted 19982.22 since round_inf should happen for digit highlighted in bold color
4)default thing
StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1)) and what is the default rounding type?
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Well, there is some gap in your understanding.
19982.2776 is rounded to 19982.28 as 76 is more than 50
19982.2776 is floored to 19982.27 and -19982.2776 will be floored to -19982.28
and similarly -19982.2876 will be ceiled to -19982.287 and +19982.2876 will be ceiled to +19982.289
if you can't make it out from the examples do let us know how are you doing the calculation.
19982.2776 is rounded to 19982.28 as 76 is more than 50
19982.2776 is floored to 19982.27 and -19982.2776 will be floored to -19982.28
and similarly -19982.2876 will be ceiled to -19982.287 and +19982.2876 will be ceiled to +19982.289
if you can't make it out from the examples do let us know how are you doing the calculation.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
Please see my example and correct me where I am wrong.
Source is one field is varchar(11)
1)Input column, output column
19982.2776, 19982.27
output column type and derivation:(decimal 7,2) and derivation:StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1), "floor")
clarification:I excepted 19982.26 since floor should happen for digit highlighted in bold color
My understanding:
If the precision is 2 digits after the decimal, then the floor will considers the digits that follow.
I considered second 7 in(19982.2776).so previous digit should be rounded down in floor case
2)Input column, output column
-19982.2876,-19982.287
output column type and derivation:(decimal 7,3) and derivation:StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1), "ceil")
clarification:I excepted -19982.288 since ceil should happen for digit highlighted in bold color
My understanding:
If the precision is 3 digits after the decimal, then the ceil will consider the digits that follow.
I considered second 6 in(-19982.2876).so previous digit should be rounded up in ceil case.so it should be -8
3)Input column, output column
19982.2345, 19982.23
output column type and derivation:(decimal 7,2) and derivation:StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1), "round_inf")
clarification:I excepted 19982.22 since round_inf should happen for digit highlighted in bold color
My understanding:
If the precision is 2 digits after the decimal, then the rounding type considers the digits that follow.
I considered 4 in(19982.2345) it is lower than 4 so previous digit(3) will be rounded to 2
4)default thing
StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1)) and what is the default rounding type?
My understanding:
No idea?
Source is one field is varchar(11)
1)Input column, output column
19982.2776, 19982.27
output column type and derivation:(decimal 7,2) and derivation:StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1), "floor")
clarification:I excepted 19982.26 since floor should happen for digit highlighted in bold color
My understanding:
If the precision is 2 digits after the decimal, then the floor will considers the digits that follow.
I considered second 7 in(19982.2776).so previous digit should be rounded down in floor case
2)Input column, output column
-19982.2876,-19982.287
output column type and derivation:(decimal 7,3) and derivation:StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1), "ceil")
clarification:I excepted -19982.288 since ceil should happen for digit highlighted in bold color
My understanding:
If the precision is 3 digits after the decimal, then the ceil will consider the digits that follow.
I considered second 6 in(-19982.2876).so previous digit should be rounded up in ceil case.so it should be -8
3)Input column, output column
19982.2345, 19982.23
output column type and derivation:(decimal 7,2) and derivation:StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1), "round_inf")
clarification:I excepted 19982.22 since round_inf should happen for digit highlighted in bold color
My understanding:
If the precision is 2 digits after the decimal, then the rounding type considers the digits that follow.
I considered 4 in(19982.2345) it is lower than 4 so previous digit(3) will be rounded to 2
4)default thing
StringToDecimal(Trim(Ln_Ext_Inp_File_Data.col1)) and what is the default rounding type?
My understanding:
No idea?
ALL of the digits are 'considered' (one by one from the right) not just the one immediately following the desired scale. And you seem to have your examples (2 after vs. 3 after) backwards, not to mention we're talking scale not precision here and you call out things like a 'second six' when there's only one.
A generic example from your data, rounding to various scales:
So, going from the original to a scale of 1 would involve all steps.
A generic example from your data, rounding to various scales:
Code: Select all
original: 19982.2776
round 3: 19982.279
round 2: 19982.28
round 1: 19982.3
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I am clear with round function but can anybody give explanation forpriyadarshikunal wrote:Well, there is some gap in your understanding.
19982.2776 is rounded to 19982.28 as 76 is more than 50
19982.2776 is floored to 19982.27 and -19982.2776 will be floored to -19982.28
and similarly -19982.2876 will be ceiled to -19982.287 and +19982.2876 will be ceiled to +19982.289
if you can't make it out from the examples do let us know how are you doing the calculation.
19982.2776 is floored to 19982.27 and -19982.2776 will be floored to -19982.28
and similarly -19982.2876 will be ceiled to -19982.287 and +19982.2876 will be ceiled to +19982.289
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Draw them on a number line (remember those from school?). All will become clear. floor moves left, ceil moves right.
Though I cannot envisage any scenario in which "+19982.2876 will be ceiled to +19982.289".
Though I cannot envisage any scenario in which "+19982.2876 will be ceiled to +19982.289".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.