DSXchange

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

From the documentation:

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.

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

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.

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?

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.

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?

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.

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

I am clear with round function but can anybody give explanation for

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

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