counting records in a PL/SQL function.
499240Sep 14 2006 — edited Sep 16 2006Hi,
I've coded a PL/SQL function that sits in an After Parameter Form trigger in an Oracle 10g Report. There are a number of parameters on the Report's Parameter Form, and this function returns the Where Clause porion of an SQL query based on which Parameters have data in them and such.
So, if if there are 10 parameters, and only 1, 2 and 4 have been filled in then the :P_WHERE_CLAUSE parameter will get the value 'AND param1 = 1 AND param2 = 2 and param3 = 3.
The client would like functionality where if there are no records returned by the query, then the Report does not run. How do I do this in PL/SQL?
In the function after the WHERE clause has been calcuated, I have tried
SELECT Count(r.batch_id) INTO btchID
WHERE r.opstamp = p.user_id AND batch_stage = 'TIN'
FROM roo_batches r, pes_users p
WHERE r.opstamp = p.user_id || :P_WHERE_CLAUSE
ORDER BY :P_ORDER_BY;
if btchID = '0' then
return (FALSE);
else
return (TRUE);
end if;
btchID is declared as a VARCHAR(3000);
This code doesn't work. btchID is always 0, even if 1 or more records that are actually being returned. Also, I get a message that says 'After Form Trigger failed'.
Can SQL statements not handle having variables in them? What other ways are there to figure out how many records are being returned in a Report?