Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Attempting to get Result as "%"

Re: Attempting to get Result as "%"

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 3 Feb 2006 06:57:16 -0500
Message-ID: <YNydnZxKhsK92H7enZ2dnUVZ_tGdnZ2d@comcast.com>

"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

 12 )
 13 AS UTILIZATION your first NVL is fine, and will give you 0 if all production olumns are NULL (the NVL is applied after the SUM)
your 2nd NVL is created the ORA-01476

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US