Apex 5.1.4
Oracle Express 11g
Can someone help me with the procedure and or my call to the procedure...?
I have a reference table with 'agent' names, and a primary table of 'referral' records.
Each referral record contains an agent.id in the referral.agentid field.
I am trying to build a dynamic pivot to return the dates of referrals and the agent names associated with
each referral - for a period... grouped by the referral date (senton). So ideally the output would be
dates name1 name2 name3 name4
09/23/19 0 1 3 0
09/24/19 4 2 0 5
Agents are grouped by office (agent.officeid); name stored as agent.name. Each office can
have different number of Agents... so part of the procedure call will eventually include the officeid
For the 'IN' part of the pivot, I pull a list of all agent names associated with the
office in question:
select LISTAGG (NAME, ',') WITHIN GROUP (ORDER BY NAME) into v_list
FROM agent
WHERE officeid=11;
This example office output is:
Alvin Ross,Betty Simons,Chanel Cooper,Daniel Scoggins,Dena Strother,Doris Hendricks,Jessica Elledge,Joshua Stephens,Terrence Simpson,Test Region
Then I add single quotes around each name:
v_alist := v_alist || '''' || replace(v_list, ',', ''',''') || '''';
resulting in:
'Alvin Ross','Betty Simons','Chanel Cooper','Daniel Scoggins','Dena Strother','Doris Hendricks','Jessica Elledge','Joshua Stephens','Terrence Simpson','Test Region'
The pivot SQL is:
select *
FROM (SELECT referral.senton, agent.name
FROM referral
join agent on referral.assignedto=agent.id)
PIVOT (Count(*) FOR name IN
('||v_alist||'))';
Each section above is working individually but I am having problems getting this all in one procedure
to return what is expected. My inexperience raises it's ugly head at this point as once I have it
in a procedure, I have difficulty diagnosing what is broken. I've borrowed an example from
https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql
I access the procedure with:
begin
print_pivot();
end;
Issues that I have identified:
1- simplest form of procedure (no group by, no from/to, no officeid) errors with
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'PRINT_PIVOT' (there are no arguments?!)
2- have yet to group by senton date
when adding group by to either part of the pivot stmt: errors with 'not a group by expression' or 'invalid identifier'
3- have yet to add code for the from/to dates and the officeid
The complete Procedure is:
create or replace PROCEDURE Print_Pivot(
pcursor OUT sys_refcursor,
presult OUT varchar2
)
AS
v_list varchar2(4000);
v_alist varchar2(4000);
v_query varchar2(4000);
BEGIN
PRESULT := 'Nothing';
-- pull list of agent names for that office then insert single quotes around names in list
select LISTAGG (NAME, ',') WITHIN GROUP (ORDER BY NAME) into v_list
FROM agent
WHERE officeid=11;
v_alist := v_alist || '''' || replace(v_list, ',', ''',''') || '''';
-- dynamic query
V_QUERY := 'select *
from (SELECT referral.senton, agent.name
FROM referral
join agent on referral.assignedto=agent.id)
PIVOT (Count(*) FOR name IN
('||v_alist||'))';
-- run query
OPEN PCURSOR
FOR V_QUERY;
PRESULT := 'Success';
Exception
WHEN OTHERS THEN
PRESULT := SQLcode || ' - ' || SQLERRM;
END Print_Pivot;