| Error in the Report Execution [message #233825] |
Fri, 27 April 2007 12:37  |
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  |
 |
Littlefoot
Messages: 21823
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
