Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Attempting to get Result as "%"
"MSWEENEY" <MSWEENEY01_at_ADELPHIA.NET> wrote in message
news:1138965754.809281.198490_at_z14g2000cwz.googlegroups.com...
>I am very much a beginner at SQL and use iSQL primarily. I have a
> fairly simple query and which performs a SUM function whereby one
> number is divided by another. I would like the answer to be provided
> as a percentage as opposed to a decimal.
>
> example:
>
> ((NVL(SUM(PRODUCTION),0)/NVL(SUM(ON_DUTY),0))-1) AS UTILIZATION
>
> PRODUCTION - 69
> ON_DUTY - 120
>
> UTILIZATION RESPONSE: 0.575
>
> DESIRED RESPONSE: 57.5%
>
> Also, I get the following error and only certain records are reported:
>
> ERROR:
> ORA-01476: divisor is equal to zero
>
> Any help is greatly appreciated!
>
> Mark
>
SQL> select (
2 ( 3 NVL( 4 SUM(PRODUCTION) 5 ,0) 6 / NVL( 7 SUM(ON_DUTY) 8 ,0 9 ) 10 ) 11 -1
you didn't post your entire query, i have to presume you either have a group by clause or our run it with different where clauses since you say that only certain records are reported -- it would be helpful to post the entire query
you'll need to use a CASE expression to convert a 0 divisor to 1 to avoide the ORA-01476
then use to_char() and the contatenation operator (, ie, ||) to format the expression
++ mcs Received on Fri Feb 03 2006 - 05:57:16 CST