Home » Developer & Programmer » Reports & Discoverer » Please see this error REP-1401 or ORA-01403
Please see this error REP-1401 or ORA-01403 [message #205763] Mon, 27 November 2006 07:53 Go to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
Rep-1401: 'CF_2Formula'; Fatal PL/SQL Error eccourred
ORA-01403 not data found.

I wrote these lines in formula there is no complied error but when I run the report shows above error.

Please help why?

function CF_2Formula return Number is
yopn number(10,2);
yrec number(10,2);
mqty number(10,2);
total number(10,2);
PR_NOT_FOUND EXCEPTION;
begin
  --- opening balance from yarn table
  Select sub1.opn, sub2.RecKGS, sub3.rmqty  into yopn,yrec,Mqty from 
   (select y.yarn_type, y.balance Opn from yarn y) sub1,
   
 (select yr.yarn_type yarnType, sum(yr.rec_kgs) RecKGS
 from sub_yarn_received yr group by yr.yarn_Type) sub2,
 
 (select p.ppno ppno, p.subpp subpp, p.req_date ETD, p.fabric_code, sum(s.req_kgs) RmQTY, p.b_code
  from  program p, sub_program s 
  where s.jobno = p.jobno  
  and req_date not between :dd and :dd1
  group by p.ppno, p.subpp, p.b_code, p.fabric_code, req_date) sub3,

(select l.fabric_code fabric_code, sl.yarn_code yarn_code, sl.cons cons
from link l, sub_link sl
where sl.jobno= l.jobno
group by l.fabric_code, sl.yarn_code, sl.cons) sub5,

(select y.yarn_code, y.yarn_type yarn from yarn y) sub6,
 
(select b.b_code, b.b_ref 
from buyer b) sub4
where sub3.b_code = sub4.b_code 
and sub5.fabric_code = sub3.fabric_code
and sub5.yarn_code=sub6.yarn_code
and sub6.yarn = :yarn
and sub2.yarnType = :yarn
and sub1.yarn_type = :yarn;

IF SQL%NOTFOUND THEN 
			RAISE PR_NOT_FOUND;
     	END IF;
     		EXCEPTION 
			WHEN PR_NOT_FOUND THEN
			null;
			
     total:= yopn+yrec+mqty;
    
     return(total);
     
     	
end;

Re: Please see this error REP-1401 or ORA-01403 [message #205821 is a reply to message #205763] Mon, 27 November 2006 16:04 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
NO-DATA-FOUND is never raised during compilation time - it depends on result data set.

One of your SELECT statements doesn't return anything (it might be either one of inline views, or the main SELECT). SQL%NOTFOUND never captures it - error is raised before the IF statement.

Therefore, write the EXCEPTION handler section below the SELECT statement and deal with the error. Something like this:
BEGIN
  SELECT column_list INTO variables
  FROM (SELECT ... ) sub1,
       (SELECT ... ) sub2,
       ...
  WHERE conditions;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    deal with it;
END;
Re: Please see this error REP-1401 or ORA-01403 [message #205846 is a reply to message #205821] Mon, 27 November 2006 21:56 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi Kamran,

hope you have solve the problem. what LF says is quite right because I have also come across this type of problem.

Bye
Ashu
Re: Please see this error REP-1401 or ORA-01403 [message #205865 is a reply to message #205846] Mon, 27 November 2006 23:33 Go to previous message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member

Thank you

Previous Topic: Dynamic where clause in web report
Next Topic: Difference in Report and Print Preview
Goto Forum:
  


Current Time: Sun Dec 11 00:28:11 CST 2016

Total time taken to generate the page: 0.06204 seconds