ORA-01476 Divisor Equal To 0
Oct 29, 2011
I am working on an application that allows a user to create a KPI formula, and the app dynamically creates a view for this formula. However as we started using it we have found that we are encountering the ORA-10476 (divisor is equal to zero) often. I have read about options of using decode or case to prevent this but it doesn't cover all our options. As a result of the fact that the user can create any equation he wants , and it can be as complicated as they want ( 2 examples:
A/(B/C-D/E) - If C or E or (B/C-D/E) are 0 the whole select will fall
A/(B-C/(D-F)) - if (D-F) or ((B-C/(D-F)) is 0 then again the select will fall.
I have seen that in MS SQL they have an option of arithabort which allows the database to return the rows that don't fall under the "divisor by 0".
either by setting something similar to arithabort, or maybe a procedure to check an equation and return the arithmetical steps in order that athey are performed?
View 2 Replies
ADVERTISEMENT
Jun 13, 2012
When running the query below, I receive this error: ERROR:ORA-01476: divisor is equal to zero.
Select Sum ( Decode ( ACCT_CLASS_NO
, 'TF', Round ( SUB_FUND_TNA_USD_IN_M, 2 )
, Null
[Code]....
how can I get around the ORA-01476: divisor is equal to zero error?
View 2 Replies
View Related
May 26, 2010
The below code gives me error (ORA-01476: divisor is equal to zero)
SELECT
((COUNT(DECODE(SUBSTR(A.ASSETNUM,6,3),'ACS','ACS',0,null))/COUNT(DECODE(SUBSTR(A.PMNUM,1,3),'ACS','ACS',0,null)))*100)
FROM WORKORDER A
WHERE TO_CHAR(A.REPORTDATE,'MON-YYYY') = :WO_DATE;
View 8 Replies
View Related
Aug 23, 2013
I have two tables e.g. test_bb and test_sub
I would like to select test_sub.price as per the below conditions. If test_bb.value_date, test_bb.vehicle matches to test_sub.value_date,test_sub.vehicle then display test_sub.price
If there is no match then as above find the closest test_sub.value_date which is less than test_bb.value_date and select corresponding test_sub.price for the same vehicle combination.
e.g.
select * from test_sub;
VEHICLE VALUE_DAT PRICE
---------- --------- ----------
10 12-APR-12 2
10 08-JAN-10 4
10 14-APR-14 6
10 06-AUG-47 8
20 24-JAN-14 10
20 20-FEB-06 12
20 18-FEB-04 14
20 28-FEB-06 16
20 22-DEC-07 19
8 rows selected.
select * from test_bb;
VEHICLE VALUE_DAT
---------- ---------
10 12-APR-12
10 10-FEB-10
20 24-JAN-14
20 22-FEB-08
Required output:
VEHICLE PRICE VALUE_DAT
---------- ---------- ---------
10 2 12-APR-12
10 4 08-JAN-10
20 10 24-JAN-14
20 19 22-DEC-07
create table test_bb(vehicle number, value_date date);
begin
insert into test_bb values(10,to_date('12-04-2012','dd-mm-yyyy'));
insert into test_bb values(10,to_date('10-02-2010','dd-mm-yyyy'));
insert into test_bb values(20,to_date('24-01-2014','dd-mm-yyyy'));
insert into test_bb values(20,to_date('22-02-2008','dd-mm-yyyy'));
end;
/
create table test_sub(vehicle number, value_date date,price number);
begin
insert into test_sub values(10,to_date('12-04-2012','dd-mm-yyyy'),2);
insert into test_sub values(10,to_date('08-01-2010','dd-mm-yyyy'),4);
insert into test_sub values(10,to_date('14-04-2014','dd-mm-yyyy'),6);
insert into test_sub values(10,to_date('06-08-1947','dd-mm-yyyy'),8);
insert into test_sub values(20,to_date('24-01-2014','dd-mm-yyyy'),10);
insert into test_sub values(20,to_date('20-02-2006','dd-mm-yyyy'),12);
insert into test_sub values(20,to_date('18-02-2004','dd-mm-yyyy'),14);
insert into test_sub values(20,to_date('28-02-2006','dd-mm-yyyy'),16);
insert into test_sub values(20,to_date('22-DEC-2007','dd-mm-yyyy'),19);
end;
/
I could write as below but I would like to know if there is a better way of doing it.
select bb.vehicle
,sub.price
,bb.value_date
from test_bb bb
,test_sub sub
where bb.vehicle=sub.vehicle
[code].........
View 8 Replies
View Related
Oct 30, 2012
I have data
Type, Month, Amount
=========
4, 1, 43333.33333
4, 2, 43333.33333
4, 3, 43333.33333
8, 5, 16000
8, 6, 16000
8, 7, 16000
8, 8, 16000
4, 2, 100
4, 3, 100and
I want to group and SUM() equal amounts and find MIN(month) and MAX(month), so the output should look like
Type, MIN(month), MAX(month), SUM(amount)
=========
4, 1, 3, 130000
8, 5, 8, 64000
4, 2, 3, 200
View 2 Replies
View Related
Jun 8, 2010
There are two tables:
create table songs(song_name text, song_artist text,song_url text, song_cat text, last_edit text);
create table categories(cat_name text, cat_total int);
im trying to create a trigger that, when i insert a new song in the songs table, it will check the category of the song (song_cat) and increase the respective cat_total (from table categories) by 1.
here is what i've done so far:
drop trigger countcat;
CREATE TRIGGER countcat AFTER INSERT ON songs FOR EACH ROW
update categories SET cat_total= cat_total +1
WHERE cat_name = (select song_cat FROM inserted);
What to write in the cat_name = (select ...). I have tried lots of stuff but still nothing. when i use this, i get the error that mydatabase.inserted doesnt exist
View 4 Replies
View Related
Sep 14, 2007
I have set up a view that pulls news & events records.
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.category, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType
FROM(so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
[Code].....
For events I want only the events that have a p.startDate equal to today. I have tried setting that line up a few different ways.
p.startDate = sysDate didn't provide any records for events
p.startDate = TO_CHAR(sysDate, 'MM/DD/YYYY') freaked the system out and nothing appeared for either parts of the union
how to output only events that are for today?
View 3 Replies
View Related
Oct 14, 2010
I'm trying to write a query that counts how many sessions are active during a 1 second time interval, then returns the maximum number of sessions active during any time interval, and all the time intervals that hit that max.
Here's a sample of the inner query results:
"INTERVAL_VALUE""SESSIONS"
"13:14:47" 13
"13:14:52" 13
"13:14:54" 13
"13:19:05" 4
"13:19:28" 4
[code]....
The max(sessions) is 13, so what I want the final output to be is:
"INTERVAL_VALUE""SESSIONS"
"13:14:47" 13
"13:14:52" 13
"13:14:54" 13
Here is the create sql for the test data:
CREATE TABLE "SESSION_TABLE"
(
"SESSIONKEY" NUMBER,
"SESSION_START_TIME" TIMESTAMP,
"SESSION_END_TIME" TIMESTAMP,
CONSTRAINT "PK_SESSIONKEY" PRIMARY KEY ("SESSIONKEY")
);
[code]....
Here is my query that works:
SELECT
maxval.interval_value,
allval.sessions,
licenselimit
FROM
(SELECT
[code]....
View 2 Replies
View Related
Aug 20, 2010
I've created a query so I can easily compare two sets of data for two different instruments:
select a.CalId, a.AtName, a.NRef, a.VaLoat, a.ValTime, a.ValRing,
cvs.NRef, cvs.CalId, cvs.AtName, cvs.VaLoat, cvs.Valtime, cvs.ValRing
from CalcAttribute a, CalcAttribute cvs
where a.NRef like '438815' and cvs.NRef like '438813'
and a.CalId *= cvs.CalId
and a.AtName *= cvs.AtName
union
[Code]...
This works great - however I want to add an addtional condition, basically so it only returns where the two are not equal.
I thought I should just be able to add an extra:
and a.ValLoat *<> cvs.ValLoat
and a.ValLoat <>* cvs.ValLoat
But it doesnt seem to like this (Incorrect syntax near '<'.)
View 3 Replies
View Related
Mar 7, 2013
This is the description for the procedure:
ADD_REWARD_sp. Given the identifier of a project, add a new reward for the project. The procedure should return a unique identifier for the reward. The month and year indicated should be greater than or equal to the current month, or an error message should be generated. The pledge amount should be greater than zero. The number of backers, if not NULL, should be greater than 1. If the project is not found, generate an error message.
This is the procedure head:
create or replace
PROCEDURE ADD_REWARD_sp(
p_proj_id IN NUMBER,
p_pledgeAmt IN NUMBER,
p_rewardDesc IN VARCHAR2,
[code]...
Basically, what I am struggling with is how to check if th month and year is greater than or equal to current month. I suppose it would be easy with just checking SYSDATE, but in this case I need to use both month and year.
View 3 Replies
View Related
Aug 6, 2013
, I'd like to create a constraint (when creating a table) that checks to ensure that the 'Year' entered is less than or equal to the current year (based off SYSDATE). Per the code below, I keep getting the same error, "missing right parenthesis". I've spent more than an hour trying different ways to get this to work, but I've been failing miserably. I am using Oracle 11g Express.
CREATE TABLE TEST (Name VARCHAR2(7) PRIMARY KEY, Year NUMBER(4), CONSTRAINT TEST_YEAR_CK CHECK (Year <= (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')))FROM DUAL);
*ERROR at line 4:ORA-00907: missing right parenthesis
View 7 Replies
View Related
Jul 14, 2011
I was getting java.sql.SQLException: executeBatch, Exception = 1when i tried to use executeBatch().could not understand why i am getting it.
View 1 Replies
View Related
