Home » Developer & Programmer » Reports & Discoverer » how to overcome this error
how to overcome this error [message #434694] Thu, 10 December 2009 04:46 Go to next message
dhinendra
Messages: 58
Registered: September 2009
Location: Gurgaon,India
Member
hi i was creating a report,it was sucessfully compiled but when i execute it,it gives the following error.

REP-1401 :CF_1FORMULA :FATAL PL/SQL ERROR OCCURED
REP-0619:-YOU CANNOT RUN WITHOUT A LAYOUT.

This is the pl/sql code which i have used--->
FUNCTION cf_1formula
   RETURN NUMBER
IS
   n1   NUMBER (20) := 0;
   n2   NUMBER (20) := 0;
   n3   NUMBER (20) := 0;
BEGIN
   SELECT   SUM (a.billqty)
       INTO n1
       FROM mrnitems a, materialreceiptnote b, partymst c
      WHERE b.partycode = c.partycode
        AND a.mrncode = b.mrncode
        AND b.unitcode = 'UC2'
        AND b.fycode = 'FY-03'
        AND b.mrnclass = 'IMPORTED'
        AND c.partyname LIKE :NAME
        AND b.challanno LIKE :challan
        AND a.mrncode LIKE :mrn
   GROUP BY a.mrncode;

   SELECT   SUM (SUM (c.reamount))
       INTO n2
       FROM vouchers b, vouchdet c, partymst a, materialreceiptnote d
      WHERE d.partycode = a.partycode
        AND b.mrncode = d.mrncode
        AND a.partyname LIKE :NAME
        AND d.challanno LIKE :challan
        AND d.mrncode LIKE :mrn
        AND b.vouchercode = c.vouchercode
        AND c.accountcode IN
               ('AC-2.061', 'AC-2.992', 'AC-2.1054', 'AC-2.038', 'AC-2.043',
                'AC-2.953')
   GROUP BY c.accountcode;

   SELECT a.billqty
     INTO n3
     FROM mrnitems a, materialreceiptnote b, partymst c
    WHERE b.partycode = c.partycode
      AND a.mrncode = b.mrncode
      AND b.unitcode = 'UC2'
      AND b.fycode = 'FY-03'
      AND b.mrnclass = 'IMPORTED'
      AND c.partyname LIKE :NAME
      AND b.challanno LIKE :challan
      AND a.mrncode LIKE :mrn;

   RETURN NVL ((n3 * n2) / n1, 0);
END;



please help me out as early as possible...
thanks

[EDITED by LF: formatted code and applied [code] tags]

[Updated on: Thu, 10 December 2009 15:48] by Moderator

Report message to a moderator

Re: how to overcome this error [message #434797 is a reply to message #434694] Thu, 10 December 2009 15:51 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Any ORA-xxxxx error? If so, which one? Is it, perhaps, divisor is equal to zero (meaning that "n1 = 0")?
Or is one of "n" variables larger than 20 digits?
Re: how to overcome this error [message #434814 is a reply to message #434694] Thu, 10 December 2009 22:59 Go to previous messageGo to next message
dhinendra
Messages: 58
Registered: September 2009
Location: Gurgaon,India
Member
hi littlefoot thanks for reply,there is no ORA-**** type error in my report but there are two errors(REP-****) which i have menstiones earlier.
Re: how to overcome this error [message #434834 is a reply to message #434694] Fri, 11 December 2009 03:07 Go to previous messageGo to next message
sameer.gokhale
Messages: 4
Registered: October 2005
Location: PUNE
Junior Member
Hi,

Please try to add the classpath into .conf file. It worked in one of my report.

Regards.
Sameer Gokhale
Re: how to overcome this error [message #434848 is a reply to message #434814] Fri, 11 December 2009 04:11 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What about the rest of my previous post?
Re: how to overcome this error [message #434850 is a reply to message #434694] Fri, 11 December 2009 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
The root error will almost certainly be ORA-1422: exact fetch returns more than requested number of rows.

The 1st and 3rd selects have exactly the same from and where clauses, but the 1st selects a sum.
For the sum to be meaningfull there have to be multiple rows that match the where clause. In which case the third query has to give that error.
Re: how to overcome this error [message #434852 is a reply to message #434694] Fri, 11 December 2009 04:41 Go to previous messageGo to next message
dhinendra
Messages: 58
Registered: September 2009
Location: Gurgaon,India
Member
hi cookiemonster thanks for your suggestion now i just edit my code like this-

FUNCTION FRIEGHTformula
RETURN NUMBER
IS
n1 NUMBER := 0;
n2 NUMBER := 0;
n3 NUMBER := 0;
BEGIN
SELECT SUM (a.billqty)
INTO n1
FROM mrnitems a, materialreceiptnote b, partymst c
WHERE b.partycode = c.partycode
AND a.mrncode = b.mrncode
AND b.unitcode = 'UC2'
AND b.fycode = 'FY-03'
AND b.mrnclass = 'IMPORTED'
AND c.partyname LIKE :NAME
AND b.challanno LIKE :challan
AND a.mrncode LIKE :mrn
GROUP BY a.mrncode;

SELECT SUM (SUM (c.reamount))
INTO n2
FROM vouchers b, vouchdet c, partymst a, materialreceiptnote d
WHERE d.partycode = a.partycode
AND b.mrncode = d.mrncode
AND a.partyname LIKE :NAME
AND d.challanno LIKE :challan
AND d.mrncode LIKE :mrn
AND b.vouchercode = c.vouchercode
AND c.accountcode IN
('AC-2.061', 'AC-2.992', 'AC-2.1054', 'AC-2.038', 'AC-2.043',
'AC-2.953')
GROUP BY c.accountcode;

SELECT a.billqty
INTO n3
FROM mrnitems a, materialreceiptnote b, partymst c
WHERE b.partycode = c.partycode
AND a.mrncode = b.mrncode
AND b.unitcode = 'UC2'
AND b.fycode = 'FY-03'
AND b.mrnclass = 'IMPORTED'
AND c.partyname LIKE :NAME
AND b.challanno LIKE :challan
AND a.mrncode LIKE :mrn
AND a.itemcode like :itmcode;

RETURN NVL ((n3 * n2) / n1, 0);
END;



and now it is working
thanks u very much for help.
Re: how to overcome this error [message #434891 is a reply to message #434834] Fri, 11 December 2009 09:34 Go to previous messageGo to next message
naveensharma11
Messages: 1
Registered: December 2009
Location: Washington DC
Junior Member

Appears there is a configuration error
Re: how to overcome this error [message #434892 is a reply to message #434891] Fri, 11 December 2009 09:44 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
naveensharma11 wrote on Fri, 11 December 2009 15:34
Appears there is a configuration error


Nope, there was an error in one of the select statements.
While we always appreciate people helping out it does pay to read the whole thread first.
Re: how to overcome this error [message #434893 is a reply to message #434852] Fri, 11 December 2009 09:51 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
@dhinendra - are you sure all those LIKE's are correct?
Unless your values for :NAME, :challan etc contain '%' or '_' then those like's should really be =

And if the like's are correct then you still run the chance of getting the same error since one of the main purposes of Like is to find multiple matches for a given string.
Re: how to overcome this error [message #434951 is a reply to message #434694] Fri, 11 December 2009 23:21 Go to previous messageGo to next message
dhinendra
Messages: 58
Registered: September 2009
Location: Gurgaon,India
Member
hi cookiemonster the thing is that I have to use 'like :' because i have a list of mrncodes and user will identify that from which MRN he needs data.now what happens when in itemcode i give a itemcode value it shows a output but when i give ' % ' then it gives error because from the formula column it is returning '1' value at a time.

RETURN NVL ((n3 * n2) / n1, 0);


If i use ' % ' then there are more than one value in 'n3' so it is returning a error.

so do you have any solution for this..

Thanks.
Re: how to overcome this error [message #435133 is a reply to message #434694] Mon, 14 December 2009 08:46 Go to previous message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm not sure exactly what you are trying to do here.
Shouldn't the user be identifying what records they want summed before this function is even run?

You need to describe what this function is trying to do. I can see that it's summing some records but since I don't know anything about your database or what this report does I can't really make any suggestions for the right approach.
Previous Topic: If possible refernce matrix cell in report builder 6i
Next Topic: how to change chart automaticly in report builder
Goto Forum:
  


Current Time: Wed Dec 07 23:58:45 CST 2016

Total time taken to generate the page: 0.14083 seconds