SQL & PL/SQL :: Divisor Is Equal To Zero Error?

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


ADVERTISEMENT

SQL & PL/SQL :: ORA-01476 - Divisor Is Equal To Zero Error

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

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 View Related

SQL & PL/SQL :: Value For Closest Or Equal Date

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

PL/SQL :: GROUP By Equal Values

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

SQL & PL/SQL :: Updating Another Table For Equal Strings

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

Set View To Show Records Equal To Date?

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

Performance Tuning :: Returning All Rows That Equal Max (value)?

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

SQL & PL/SQL :: Outer Table Join With Not Equal Condition

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

SQL & PL/SQL :: Checking If Month And Year Is Greater Or Equal To Current One?

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

PL/SQL :: Create Constraint That Checks To Ensure (Year) Entered Is Less Or Equal To Current?

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

JDeveloper, Java & XML :: Java.sql.SQLException - Execute Batch - Exception Equal To 1?

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

Error - ORA-06521 - PL/SQL Error Mapping Function

Nov 15, 2010

Oracle version - 10.1.0.4 OS - HP-UX

In my alert log I am getting the following error quite repeatedly; My application hangs, and have to restart the instance.

CODEORA-00604: error occurred at recursive SQL level 1
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
ORA-06512: at line 6

View 4 Replies View Related

Error While Exporting A Job

Aug 6, 2010

error while exporting a job

ORA-39171- job is experiencing a resumable wait
ORA-01652-UNABLE TO EXTEND TEMP SEGMENT BY 128 IN TABLESPACE TEMP.

View 2 Replies View Related

Getting Error - Too Many Values

Jul 23, 2010

im new to Database i have to count no of messages based on the minuties like 1-5, 6-15,16-30,>30 min how to write query

select count(*) from table1
where (
select
( (extract(day from column2)-extract(day from column1))*24*60 +
(extract(hour from column2)-extract(hour from column1))*60 +
extract(minute from column2)-extract(minute from column1)) as Processed_time from table1
) IN (1,5)

getting an error TOO MANY VALUES

View 2 Replies View Related

SQL & PL/SQL :: Compilation Error In 11.2.0.3

Jun 6, 2011

In oracle 11.2.0.3 I am getting, PL/SQL:ORA-00904 TR_STATE_NAME invalid identifier

but the same code compiled without any compilation error (though column name specified was wrong).I corrected the column name and its working fine in 11.2.0.3 now. Is there any enhancement in Oracle Version 11.2.0.3 in terms of compilation error?

View 6 Replies View Related

SQL & PL/SQL :: Error While Using Cursor

Apr 10, 2013

My task has more lines of code,so i tried to present here only few lines of code where i am getting the error.The following cursor needs to select data from two tables with some conditions that are included in cursor and place those in other table.I used BULK collect.

CURSOR c_arch_trk (p_run_date DATE,
p_nbr_days_arch1 NUMBER,
p_nbr_days_arch2 NUMBER )
IS
SELECT a.SIS_PGM_START_DATE,a.SIS_PGM_END_DATE,a.PGM_MSTR_NBR,a.PGM_TRK_NBR,a.CNTL_LOCN,a.CMPNY_VNDR_NBR,a.AGRMNT_MSTR_NBR,
a.SLS_CONT_NBR,b.PGM_NAME,b.PGM_BASIS,b.AGRMNT_CNTL_LOCN
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Constraint - Getting Error?

Aug 14, 2013

Create table students
(
Sid number(3),
Sname varchar(20) not null,
Email varchar(20) not null,
Doj Timestamp default sysdate not null,
Dob date not null,

[code]....

when i run the above code i m getting error like this...I dont understand wat error it is...Because i didnt reference any column in the check constraint...Thn why it says that i m referencing some column..

Error:
SQL>
SQL> Create table students
2 (
3 Sid number(3),
4 Sname varchar(20) not null,

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Error In Execute Immediate?

May 10, 2013

I have attached my Code and log records, here while calling Execute Immediate the program throws an error.

View 4 Replies View Related

SQL & PL/SQL :: What Is Mutating Error

Sep 2, 2005

What is Mutating error,have u faced it, I have faced this question so many time in interviews but i have not facing this problem till now.

View 15 Replies View Related

SQL & PL/SQL :: Error With Creating Job

Sep 18, 2010

I am writing the following PLSQL code-

BEGIN

DBMS_SCHEDULER.CREATE_JOB
(
job_name=>'TRIAL JOB',
job_type=>'PLSQL BLOCK',
job_action=>
[code]........

The new table used above has the following structure

Name Null? Type
STAMP TIMESTAMP(6) WITH TIME ZONE

However I get the following error on execution and am not able to make out why?

BEGIN
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 3

View 4 Replies View Related

SQL & PL/SQL :: Getting Error With Trigger

Jul 20, 2011

I just create a trigger to fire whenever the quantity on hand for a books table has been updated.when the quantity on hand become zero ,it should call 'insert_reorder' procedure and pass the ISBN for the books. This is my code

CREATE OR REPLACE TRIGGER books_qty_on_hand_trg
AFTER UPDATE OF on_hand_quantity ON books
FOR EACH ROW
BEGIN
IF :NEW.on_hand_quantity = 0 THEN
insert_reorde_pp (:NEW.isbn)
END IF;
END;

But i m getting this error

LINE/COL ERROR
4/3 PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; The symbol ";" was substituted for "EN D" to continue.

View 5 Replies View Related

SQL & PL/SQL :: Getting PLS-00201 Error?

May 21, 2012

I have a job to run 'scott.product_pkg' daily, but I am getting this following error.

Job failure message:
ORA-06550: line 1, column 8:
PLS-00201: identifier 'scott.product_pkg' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored

View 4 Replies View Related

SQL & PL/SQL :: Getting Error By Using NVL() And Loop

Sep 22, 2012

Declare
x number;
Begin
Loop
if NVL(x,1)>=1 then
[code].......

May be this code is going into an infinite loop. I'm unable to understand where the problem is?

View 6 Replies View Related

SQL & PL/SQL :: Mutating Error?

Oct 20, 2013

1.I have created table emp_log using structure of emp table

create table emp_log
as select * from emp
where 1=2;

2.Now I have added some new fields i.e
new_sal(updated salary) ,
upd_by (who updated),
upd_date (Salary update date)
alter table emp_log
add (new_sal number, upd_by varchar2(20),upd_date date);

3. I have made following trigger to insert old and new values (for salary,user and date) in emp_log table whenever I update sal for particular employee in emp table and at the same time i am selecting the updated value of sal into lv_sal variable which will cause mutating error.

create or replace trigger emp_trg
after update of sal on emp
for each row
declare

[code]...

4. Suppose sal of emp is 1000:-
update emp
set sal=sal+100
where empno=7369;
>select * from emp_log;
output:- sal= 1000 new_sal=1100

[code]...

Now the question starts if i commment pragma autonomous_tansaction and commit statement then it gives mutating error because at the same time i am updating and selecting value of sal from emp table.

create or replace trigger emp_trg
after update of sal on emp
for each row
declare

[code]...

Questions:-
1.Is pragma autonomous_transaction handling the mutating error and if yes then how?
2.How many ways are there to handle mutating error.
3.What is the exact definition of Mutating Error.
4.An example or query to use lv_sal variable for printing new updated sal.
5.What is Mutating Table , Is it similar to mutating error (Found this topic while searching for mutating error).

View 8 Replies View Related

SQL & PL/SQL :: Error Using Dbms-lob?

Mar 6, 2013

I am begginer programing oracle and I have a issue to resolve but I can't resolve it.I have a procedure that upload a image from a directory. But when I pass the path, the function bfilename put a slash "/" in the path. I don't know why.Here my code.

CREATE OR REPLACE PROCEDURE p_grava_assinatura_gestor AS
---------------------------------------------------------
-- Crio o Cursor dos Nomes dos Arquivos JPG. --
---------------------------------------------------------
CURSOR cursor_nome_arquivo IS
SELECT p.id, (p.empresa || '_' || p.chapa || '.JPG') AS noarq

[code]...

View 9 Replies View Related

SQL & PL/SQL :: Error While Execution

Jul 23, 2010

When i am trying to execute the below in sql. i am getting the error.

create or replace type sum_n as object (
nodes node_d,
constructor function sum_n return self as result,
member procedure do_s (m date,exd varchar)
);
/

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
2/9 PLS-00201: identifier 'NODE_d' must be declared

View 2 Replies View Related

SQL & PL/SQL :: Without Error - Keep RowID?

Oct 9, 2013

CREATE TABLE CD_TRANS_GLIDE
( "TRANS_GLIDE_ID" CHAR(32) NOT NULL ENABLE,
"CARD_SEQ_NO" CHAR(10),
"SUBACCOUNT_NO" VARCHAR2(10),
"CASH_FLAG" CHAR(1),

[code]....

View 7 Replies View Related

PL/SQL :: Getting Ora-12170 Error?

Aug 20, 2012

In my database while querying the below sql statement i am getting the ora-12170 error.

select AVAIL_QTY from CCH_AVAILABLE_QUANTITY;

when i used the describe command

DESC CCH_AVAILABLE_QUANTITY; it is executing and showing the 'AVAIL_QTY' column in the output.

i tried using all_tab_columns, all_tables to identify the schema of this table. the strange thing i found is

select * from all_tab_columns where column_name like '%AVAIL_QTY%'; this line is giving showing me the table name in the output.

but,

select * from all_tables where table_name like 'CCH_AVAILABLE_QUANTITY'; this line is executing but not giving me the out put.

View 2 Replies View Related

SQL & PL/SQL :: Sequence - Getting Error?

May 13, 2011

i have create one sequece using this syntax

CREATE SEQUENCE seq_emp_id START WITH 100;

after that when i create a table using this syntax

CREATE TABLE employee
(
emp_id integer default NEXTVAL ('seq_emp_id'),
name text
);

i got an error like this
*
ERROR at line 2:
ORA-04044: procedure, function, package, or type is not allowed here

so how can i resolve my this error

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved