Round to whole number

[Former Member]

Hi,

I have to round the decimal to four places by multiplying with 10, 100, 1000 etc and make it a whole number in the output.

For example if the value is 0.1234 i have to multiply with 10,000 to make it 1234. If it is 12.34 then multiply with 100 to make it 1234 etc.

Appreciate your suggestions.

Arun

[Former Member]

You can achieve the same by casting it to a varchar, replacing the decimal point with null and then casting it back to decimal.

Try this in Query transform

rtrim(cast(replace_substr(cast(DECIMAL_COLUMN,'varchar(20)'),'.',''),'decimal(20,10)'),'0')

Regards,

Suneer

[Former Member]

Suneer,

I have one input column and two output column (i missed mentioning the second one, apologize for inconvenience).

The input column (source.col1) has values like 0.01, 0.2345, etc.

In the output column1 (output.col1) i have to have values like 1, 2345 and in the second output column (output.col2) the values should be 100, 10,000 which is the multiplication factor.

How can i incorporate both?

Thanks,

Arun

[Former Member]

Suneer,

Thank you Dav. My understanding of logic seems wrong.

Actually client needs in this way:

For example -

If input.col1 = 10 then output.col1 = 10 and output.col2=1

If input.col1 = 10.5 then output.col1 = 105 and output.col2=10

If input.col1 = 0.1234 then output.col1 = 1234 and output.col2=10,000

If input.col1 = 0.12345 (greater than 5 digits) then do not load.

Appreciate your help.

Arun

[Former Member]

Problem Solved.

Thanks,

Arun