how to overcome this error [message #434694] |
Thu, 10 December 2009 04:46 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #434850 is a reply to message #434694] |
Fri, 11 December 2009 04:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #434892 is a reply to message #434891] |
Fri, 11 December 2009 09:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
naveensharma11 wrote on Fri, 11 December 2009 15:34Appears 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 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.
|
|
|