Solved

# Formula SQL/Pl

Posted on 2007-10-11
388 Views
Hi,
I have not written a formula in Oracle -SQL/Pl could you give me the syntax.
Field 1 / Field 2 = % of Errors
0
Question by:syhctl

LVL 18

Accepted Solution

declare
v_percent_errors number;
begin
select round((field_1/ field_2)*100,2)
into v_percent_errors
from my_table
where rownum = 1;

dbms_output.put_line( 'Error percentage : ' || v_error_percent );
end;

will that do you?  it is an anonymous plsql block that works out the error percentage for the first record in my_table.

good luck :)
0

LVL 9

Expert Comment

probably you need RATIO_TO_REPORT function

Example 21-13 RATIO_TO_REPORT

To calculate RATIO_TO_REPORT of sales for each channel, you might use the following syntax:

SELECT ch.channel_desc, TO_CHAR(SUM(amount_sold),'9,999,999') AS SALES,
TO_CHAR(SUM(SUM(amount_sold)) OVER (), '9,999,999') AS TOTAL_SALES,
TO_CHAR(RATIO_TO_REPORT(SUM(amount_sold)) OVER (), '9.999')
AS RATIO_TO_REPORT
FROM sales s, channels ch
WHERE s.channel_id=ch.channel_id  AND s.time_id=to_DATE('11-OCT-2000')
GROUP BY ch.channel_desc;

CHANNEL_DESC         SALES      TOTAL_SALE RATIO_
-------------------- ---------- ---------- ------
Direct Sales             14,447     23,183   .623
Internet                    345     23,183   .015
Partners                  8,391     23,183   .362
0

## Featured Post

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.