Home » Developer & Programmer » Reports & Discoverer » Error in the Report Execution
Error in the Report Execution [message #233825] Fri, 27 April 2007 12:37 Go to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member
Hi,

In my Report i created a paramater and gave a pl/sql code in validation trigger for that parameter.

My intension is

if user select 'yes' in the report parameter at run time, it has to show the output records from amounts table in which balance column value is ZERO.


If user select 'NO' in the report parameter at run time, it has to show the output records from amounts table in which balance column value should not be equal to ZERO.

I am using the fallowing table.


SQL> SELECT * FROM AMOUNTS;

   INVAMT   PAIDAMT       BAL
--------- --------- ---------
    10000      8000      2000
    30000     15000     15000
    40000     40000         0


pl/sql code in validation trigger for my parameter is


function P_COMPLETEValidTrigger return boolean is
CURSOR C1 IS SELECT * FROM AMOUNTS;
V_NO NUMBER;
V1_NO NUMBER;
V2_NO NUMBER;
CROW AMOUNTS%ROWTYPE;
begin
FOR CROW IN C1 LOOP
IF :P_COMPLETE='YES' THEN
  	
 SELECT CROW.INVAMT,CROW.PAIDAMT, CROW.BAL INTO V_NO,V1_NO,V2_NO FROM AMOUNTS A WHERE A.BAL=0;

 DBMS_OUTPUT.PUT_LINE(V_NO||V1_NO||V2_NO );
 ELSE
  return (FALSE);
 END IF;
	END LOOP;
		
end;


when i run the report it showing the fallowing error LIKE

--PL/SQL FUNCTION RETURN WITHOUT VALUE

When i run the report by changing the select stmt in my validation trigger like
SELECT CROW.INVAMT,CROW.PAIDAMT, CROW.BAL INTO V_NO,V1_NO,V2_NO FROM AMOUNTS A WHERE A.BAL<>0;

it shows error like

--Exact fetch returns mor then requested no of rows.

Could any body help me in this.

Thanks in advance.

Re: Error in the Report Execution [message #233868 is a reply to message #233825] Fri, 27 April 2007 17:07 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First something about the error you got: function MUST return a value. Your function returns a value (FALSE) only if parameter :p_complete is different from 'yes'. It returns nothing if :p_complete = 'yes'. As you declared it as BOOLEAN, it must return either TRUE or FALSE - it can not return a record (at least, that's what I figured out from your words).

Another error is TOO-MANY-ROWS. It is a result of more than one record which has 'bal' column value different from 0. Solve it using EXCEPTION handler, or using one of aggregate functions as MIN, MAX and similar.

Now, back to your problem. PL/SQL is not necessary here - all this can be done in a query which would look like this:
SELECT invamt, paidamt, bal
FROM AMOUNTS
WHERE SIGN(bal) = DECODE(:p_complete, 'yes', 0, 1)

Translated to English: compare sign of the 'bal' column with decoded value of the parameter. If :p_complete = 'yes', make it 0 and return all records whose 'bal' column value = 0.

Test it and see whether it helps.
Re: Error in the Report Execution [message #233974 is a reply to message #233825] Sat, 28 April 2007 11:51 Go to previous message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member
Hi,

I got it.

Thanks
Previous Topic: editing margin - frames visible around field
Next Topic: dynamically image indepent on the parameter
Goto Forum:
  


Current Time: Sun Dec 11 04:32:00 CST 2016

Total time taken to generate the page: 0.09918 seconds