Putting ‘$’(Dollar) sign or ‘%’( Percent) sign is not a complex task as coming to a figure where you would like to have this prefixed or suffixed. So, first make sure the ground work is done.

If you are calculating a division on the fly and the numerator is greater than the denominator than there is a chance that you would not see the result as expected. This happens due to unconverted numeric data types. First make sure you have converted it to decimal as follows;

cast (<numerator> as decimal(18,8))/cast(<denominator> as decimal(18,8))

In this decimal() function there are two parameters, 1st parameter is the total number of digits that can be displayed and the 2nd parameter is the number of digits out of the total number that can be displayed “before” the decimal. Remember it is before the decimal point and NOT after the decimal.

But while trying this you might get an error “2618:  Invalid calculation:  division by zero.”, in this case add the following nullifzero() function,

(nullifzero(cast(numerator as decimal(18,8))))/(nullifzero(cast(denominator as decimal(18,8))))

Some time you get errors like “Numeric overflow occurred during computation.”, this is a situation when the number of digits after the decimal is less than what the division operation produces. Increase the number by reducing the 2nd parameter in the decimal function.

Here is a complete one line code.

SELECT (nullifzero(cast(12525 as decimal(18,8))))/(nullifzero(cast(111100 as decimal(18,8))))

After the decimal calculation is taken care, displaying the “signs” is really easy, for ‘%’( percentage) just append the double pipe with ‘%’ in single quote. As follows;

SELECT (nullifzero(cast(12525 as decimal(18,8))))/(nullifzero(cast(111100 as decimal(18,8)))) || ‘%’ as “PercentDisplay”

One more way with converting the entire result set to CHAR;

SELECT cast(((nullifzero(cast(12525 as decimal(18,8))))/(nullifzero(cast(111100 as decimal(18,8)))) (format ’9.99999999%’)) as char(15) )  as “PresentDisplay”

And to display ‘$’ (dollar) sign, prefix this sign before the calculation as follows;

SELECT ‘$’ || (nullifzero(cast(12525 as decimal(18,8))))/(nullifzero(cast(111100 as decimal(18,8)))) as “DollarDisplay”

With converting the entire result set to CHAR;

SELECT cast(((nullifzero(cast(12525 as decimal(18,8))))/(nullifzero(cast(111100 as decimal(18,8)))) (format ‘$$$,$$$.99999999′)) as char(10) )  as “DollarDisplay”

Our Random Articles

More Links