I don't know if this is possible but here goes. I only want to used 1 stored procedure for a crystal reports. I want to pass a parameter to the
stored procedure so that it will only display on ref cursor. Code looks like this. The first cursor shows up fine and the parameter works but I need to display the 2nd, 3rd cursor. Any help would be appreciated.
create or replace
procedure BRR_Total(
--parameter1 in varchar2,
main_resultset in out sys_refcursor,
approval in out sys_refcursor,
review_status in out sys_refcursor)--,
--cur in out sys_refcursor)
is
begin
open main_resultset for
select distinct
mo.entitykey mo_entitykey,
mo.batchnumber mo_batch_number,
mo.moid mo_moid,
mo.quantity mo_quantity,
mo.mostatus mo_status,
mo.storageunit mo_storage_unit,
mo.validfrom mo_valid_from,
mo.validto mo_valid_to,
mo.materialname mo_materialname,
mo.materialnumber mo_materialnumber,
where brr.entitykey = 5063875434.00;
-- where brr.entitykey = parameter1;
-- and pro.pk_sprache = parameter1;
OPEN approval for
SELECT
RS.PARENTKEY,
RS.SIGNDATE,
RS.RELEASABLESTATUS,
RS.SIGNATURETEXT,
RS.SIGNATURECOMMENT,
RS.SORTORDER,
RS.RELEASABLETYPE,
RS.RELEASABLETYPEGOUPIDENTIFIER,
RS.MBRFULFILLED,
RS.USERREPORTVALUE,
RS.ENTITYKEY
FROM PASX.PR_EBR_BRR_RELEASE_SIGNATURES RS;
OPEN review_status for
SELECT
RS.ENTITYKEY,
RS.REVIEWSTEPDESCRIPTION,
RS.REVIEWCONDUCTED,
RS.SORTORDER,
RS.ELEMENTCOUNTREPORTVALUE,
RSS.SORTORDER,
RSS.ENTITYKEY,
RSS.SIGNATURETEXT,
RSS.SIGNDATE,
RSS.USERREPORTVALUE,
RSS.PK_PROTOKOLLKEY
FROM PASX.PR_EBR_BRR_REVIEW_STEP RS
LEFT OUTER JOIN PASX.PR_EBR_BRR_RELEASE_SIGNATURES RSS
ON (RS.ENTITYKEY = RSS.PARENTKEY)
AND (RS.PK_PROTOKOLLKEY = RSS.PK_PROTOKOLLKEY);
--approval:=cur;
end;
I don't know how Crystal Reports interacts with stored procedure ref cursors. But if your question is:
How can I consume different queries based on an input parameter?
Then you can have your ref cursor return the result of a different query based on your input:
create or replace procedure p (
query_selector in int,
results out sys_refcursor
) is
begin
if query_selector = 1 then
open results for select query_1 ...
elsif query_selector = 2 then
open results for select query_2 ...
elsif ...
end if;
end p;