Forum Stats

  • 3,853,861 Users
  • 2,264,287 Discussions
  • 7,905,488 Comments

Discussions

Categories

Dynamic Sql pivot procedure - HELP!

RichDW
RichDW Member Posts: 75 Green Ribbon
edited Oct 18, 2019 10:46AM in APEX Discussions

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;

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,217 Red Diamond
    edited Oct 17, 2019 6:54PM Answer ✓ 
    RichDW wrote:Bottom line is that it seems the 'Function body returning sql' will not work if you have a page item (or variable) in your code. Hard code the value instead and it works fine. I've altered the code a bit, moving the query to pull the 'IN' list of names used in the pivot query from the AGENT table into a function - returning a list - which is used in the function to build and return the sql query.The page item P60_WG provides the 'workgroup' number - it is hard coded in the function to 'ESU' or '11' - On the form, the dropdown select would be 'ESU' and then refresh the page if you have to after page load to get it into session.

    The problem is related to the use of a page item in the query generation function, but only indirectly. The real issue is having the region Use Generic Column Names property set to No. If the workgroup ID is hard-coded the function returns a valid list of 10 agents at design time. APEX uses this to generate the pivot query with the SENTON column + a fixed set of 10 agent columns (look at the column list in Page Designer). If a page item is used instead, this has no value in session state in the builder at design time, the function returns an empty list of agents, and the APEX generated pivot query projection only contains the SENTON column.

    The ORA-01403 runtime error was probably caused by the report being sorted by a column that no longer existed after it was regenerated to use the bind variable.

    When creating dynamic pivot reports using the PL/SQL Function Body returning SQL Query you need to set Use Generic Column Names to Yes, with Generic Column Count set to the maximum number of columns that will be returned. See example on page 586 of your demo application.

    There are a few other things that can be tidied up:

    • Using Execute PL/SQL Code with null; code blocks to set parameter values in session state values via the Items to Submit property is seriously obsolete. Just list the parameter items in the Page Items to Submit source property of the relevant region(s).
    • Individual refresh dynamic actions on each parameter item aren't necessary. As these items are all in the same region, a single Change action at region level can be used to handle all of them.
    • Adding double-quoted column aliases to the pivot column list enables the basic Column Names heading option to be used, dispensing with the need for a repetitive PL/SQL Function Body block.
    · Share on TwitterShare on Facebook
«1

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,217 Red Diamond
    edited Oct 4, 2019 6:36PM 
    RichDW wrote:Apex 5.1.4
    Oracle Express 11gCan 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 bedates name1 name2 name3 name4
    09/23/19 0 1 3 0
    09/24/19 4 2 0 5Agents 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 officeidFor 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 RegionThen 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-sqlI access the procedure with:begin
    print_pivot();
    end;

    Access it from where?

    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?!)

    According to the procedure definition there are two OUT parameters, and these are not supplied in the call above.

    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 listselect 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;

    These issues are only really of academic interest as a procedure returning a REF CURSOR result is effectively useless in APEX.

    Instead, create a classic report region with a Function body returning SQL query data source that generates and returns the text of the dynamic pivot query:

    (The WHEN OTHERS clause is useless too...)

    · Share on TwitterShare on Facebook
  • RichDW
    RichDW Member Posts: 75 Green Ribbon
    edited Oct 9, 2019 2:48PM

    fac586 - thank you for the direction.

    I changed the report type to 'Classic Report (based on Function)' with source 'PL/SQL Function Body returning SQL Query':

    Declare
    v_list varchar2(4000);
    v_alist varchar2(4000);
    v_query varchar2(4000);

    BEGIN

    --  pull names from table column

    select LISTAGG (NAME, ',') WITHIN GROUP (ORDER BY NAME)  into v_list 
       FROM agent
    --   WHERE officeid = NV('P60_WG');
    --   WHERE officeid = :P60_WG;
      WHERE officeid = 11;

    --  concatenate names in list inserting single quotes
    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||') )';

    return(v_query);

    END;

    I also changed the Attributes:Heading:Type  from Custom Headings to PL/SQL Function Body:

    Declare
    v_list varchar2(4000);
    v_alist varchar2(4000);

    BEGIN

    --  pull names from table column
    select LISTAGG (NAME, ',') WITHIN GROUP (ORDER BY NAME)  into v_list 
       FROM agent
       WHERE officeid = NV('P60_WG');

    --  concatenate names in list inserting Colon between names
    v_alist := v_alist ||  'SENTON:' || replace(v_list, ',', ':');

    return(v_alist);

    End;

    With a hard coded value in my report SQL (WHERE officeid=11;)

    and using a Bind variable in the PLSQL for the header query (WHERE officeid =NV('P60_WG';) )

    the report works well (disregard the parameters region at this time):

    output-HardcodedQuery-CustomheadingBind.JPG

    However when using a bind variable in the report SQL (WHERE officeid= NV('P60_WG');) - I get an error:  
    failed to parse SQL query:
    ORA-01403: no data found

    Why am I having trouble using a page item value within the PL/SQL function?

    Here is the debug for the failed report using a bind variable:

    <td style="border-width: 1pt medium medium 1pt; border-style: sol

    Execution

    Message

    1. 0.00000

    Reset NLS settings

    1. 0.00000

    alter session set
      NLS_COMP='BINARY' NLS_SORT='BINARY' NLS_CALENDAR='GREGORIAN'
      NLS_TERRITORY='AMERICA' NLS_LANGUAGE='AMERICAN'

    1. 0.00000

    ...NLS: Set Decimal separator="."

    1. 0.00000

    ...NLS: Set NLS Group separator=","

    1. 0.00000

    ...NLS: Set g_nls_date_format="DD-MON-RR"

    1. 0.00000

    ...NLS: Set g_nls_timestamp_format="DD-MON-RR
      HH.MI.SSXFF AM"

    1. 0.00000

    ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR
      HH.MI.SSXFF AM TZR"

    1. 0.00000

    ...Setting session time_zone to -04:00

    1. 0.00000

    R E Q U E S T show

    1. 0.00000

    Language derived from: FLOW_PRIMARY_LANGUAGE, current
      browser language: en

    1. 0.00000

    alter session set nls_language='AMERICAN'
      nls_territory='AMERICA'

    1. 0.00000

    NLS: CSV charset=WE8MSWIN1252

    1. 0.00000

    ...NLS: Set Decimal separator="."

    1. 0.00000

    ...NLS: Set NLS Group separator=","

    1. 0.00000

    ...NLS: Set g_nls_date_format="DD-MON-RR"

    1. 0.00000

    ...NLS: Set g_nls_timestamp_format="DD-MON-RR
      HH.MI.SSXFF AM"

    1. 0.00000

    ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR
      HH.MI.SSXFF AM TZR"

    1. 0.00000

    Setting NLS: NLS_DATE_FORMAT="DD-MON-RR
      HH24:MI"

    1. 0.00000

    ...NLS: Set g_nls_date_format="DD-MON-RR
      HH24:MI"

    1. 0.00000

    ...NLS: Set g_nls_timestamp_format="DD-MON-RR
      HH.MI.SSXFF AM"

    1. 0.00000

    ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR
      HH.MI.SSXFF AM TZR"

    1. 0.00000

    NLS: Language=en

    1. 0.01600

    Application 201, Page Template: 65224001814607015

    1. 0.00000

    Authentication check: Custom_Security (NATIVE_CUSTOM)

    1. 0.00000

    Session State: fetch from database (exact)

    1. 0.00000

    ... sentry+verification success

    1. 0.00000

    ...Session ID 2409047431057 can be used

    1. 0.00000

    ...Setting session time_zone to -04:00

    1. 0.00000

    ...Check for session expiration:

    1. 0.00000

    Branches - point: BEFORE_HEADER

    1. 0.00000

    Display Page from Cache

    1. 0.00000

    Fetch application meta data

    1. 0.00000

    Setting NLS: NLS_DATE_FORMAT="DD-MON-RR
      HH24:MI"

    1. 0.00000

    ...NLS: Set g_nls_date_format="DD-MON-RR
      HH24:MI"

    1. 0.00000

    ...NLS: Set g_nls_timestamp_format="DD-MON-RR
      HH.MI.SSXFF AM"

    1. 0.00000

    ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR
      HH.MI.SSXFF AM TZR"

    1. 0.00000

    ...http header processing

    1. 0.00000

    ...set mime type: text/html

    1. 0.00000

    ...set additional http headers

    1. 0.00000

    Process point: BEFORE_HEADER

    1. 0.00000

    Processes - point: BEFORE_HEADER

    1. 0.00000

    ...close http header

    1. 0.00000

    Show page template header

    1. 0.00000

    Load JavaScript
      file=/i/libraries/jquery/1.12.3/jquery-1.12.3.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/jquery/2.2.3/jquery-2.2.3.js?v=5.1.4.00.08

    1. 0.00000

    Set RequireJS define jquery=apex.jQuery

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/core.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/debug.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/util.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/locale.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/lang.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript

      file=/i/libraries/apex/message.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=wwv_flow.js_messages?p_app_id=201&p_lang=en&p_version=5.1.4.00.08-21551848&p_builder=Y

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/storage.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/navigation.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/event.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/server.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/page.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/region.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/item.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/apex_5_0.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/widget.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/widget.util.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/dynamic_actions_core.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/dynamic_actions.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/jquery-ui/1.10.4/ui/jquery-ui-1.10.4.custom.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/actions.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/widget.checkboxAndRadio.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/widget.popupLov.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/widget.selectList.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript

      file=/i/libraries/apex/widget.textarea.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/widget.yesNo.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/widget.menu.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/widget.toggleCore.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/widget.collapsible.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/tooltipManager.js?v=5.1.4.00.08

    1. 0.00000

    Load JavaScript
      file=/i/libraries/apex/theme.js?v=5.1.4.00.08

    1. 0.00000

    Rendering form open tag and internal values

    1. 0.00000

    Evaluate which regions should be rendered for display
      point REGION_POSITION_07

    1. 0.00000

    ...No regions to render

    1. 0.00000

    Authorization Check: "MUST_NOT_BE_PUBLIC_USER"
      Component: ""

    1. 0.00000

    ...  passed

    1. 0.00000

    Authorization Check: "MUST_NOT_BE_PUBLIC_USER"
      Component: ""

    1. 0.00000

    ...  passed

    1. 0.00000

    Authorization Check: "MUST_NOT_BE_PUBLIC_USER"
      Component: ""

    1. 0.00000

    ...  passed

    1. 0.00000

    Authorization Check:
      "CC-MGMT/SUPV/LEAD/STATE/REGION/QA" Caching:
      "BY_USER_BY_SESSION" Component ""

    1. 0.00000

    ...  passed (using
      cache)

    1. 0.00000

    Authorization Check: "CC-MGMT/SUPV/LEAD/QA"
      Caching: "BY_USER_BY_SESSION" Component ""

    1. 0.00000

    ...  passed (using
      cache)

    1. 0.00000

    Authorization Check: "CC-MGMT" Caching:
      "BY_USER_BY_SESSION" Component ""

    1. 0.00000

    ...  passed (using
      cache)

    1. 0.00000

    Authorization Check: "CC-MGMT" Caching:
      "BY_USER_BY_SESSION" Component ""

    1. 0.00000

    ...  passed (using
      cache)

    1. 0.00000

    Authorization Check: "CC-MGMT/SUPV" Caching:
      "BY_USER_BY_SESSION" Component ""

    1. 0.00000

    ...  passed (using
      cache)

    1. 0.00000
    · Share on TwitterShare on Facebook
  • fac586
    fac586 Senior Technical Architect Member Posts: 21,217 Red Diamond
    edited Oct 9, 2019 3:02PM

    Both debug traces are using hard-coded parameters...

    · Share on TwitterShare on Facebook
  • RichDW
    RichDW Member Posts: 75 Green Ribbon
    edited Oct 9, 2019 4:49PM

    Both debug traces do use bind variables, however

    in the report that works there is a hard coded value in the Report SQL and a BIND variable in the Header SQL.

    in the report that does not work, there is a BIND variable in both the Report AND Header SQL - ??

    NV('P60_WG') = 11

    In the failed debug I do see something that only shows up (added by Apex) when using the Bind variable:  order by 1 desc   nulls last

    parse query as: DEV

    1. 0.01600

    ...Execute Statement: select *

           from (SELECT
      referral.senton, agent.name

                 FROM
      referral  

                   join
      agent on referral.assignedto=agent.id)

                 PIVOT
      (Count(*) FOR name IN ('Alvin Ross','Betty Simons','Chanel Cooper','Daniel
      Scoggins','Dena Strother','Doris Hendricks','Jessica Elledge','Joshua
      Stephens','Terrence Simpson','Test Region') )

    order by 1 desc
      nulls last

    · Share on TwitterShare on Facebook
  • fac586
    fac586 Senior Technical Architect Member Posts: 21,217 Red Diamond
    edited Oct 9, 2019 5:16PM

    Sorry, didn't see the differences in the commented-out lines.

    However, I can't see any error messages either. Where and when does the ORA-01403 exception occur? Does it appear in any of the debug traces?

    · Share on TwitterShare on Facebook
  • RichDW
    RichDW Member Posts: 75 Green Ribbon
    edited Oct 15, 2019 1:34PM

    I've been out of office for a few days -

    I do not get any error message in the debug.  The error only shows within the report at run time.  Errors only when using a bind variable in the data sql.

    · Share on TwitterShare on Facebook
  • fac586
    fac586 Senior Technical Architect Member Posts: 21,217 Red Diamond
    edited Oct 15, 2019 4:53PM 
    RichDW wrote:I've been out of office for a few days -I do not get any error message in the debug. The error only shows within the report at run time. Errors only when using a bind variable in the data sql.

    If debug mode is enabled I would expect to see the error somewhere in the debug log. Are you checking the entries for AJAX requests as well as page show processing?

    I can't reproduce any problem when using an NV() reference in a pivot report query. Suggest you replicate what you are doing in an example on apex.oracle.com.

    · Share on TwitterShare on Facebook
  • Mike286
    Mike286 Member Posts: 100 Blue Ribbon
    edited Oct 16, 2019 2:11PM

    Have a look at this:

    https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

    · Share on TwitterShare on Facebook
  • RichDW
    RichDW Member Posts: 75 Green Ribbon
    edited Oct 17, 2019 4:35PM

    I've read that - however it too does not work with a page item variable.

    · Share on TwitterShare on Facebook
  • RichDW
    RichDW Member Posts: 75 Green Ribbon
    edited Oct 17, 2019 4:47PM

    fa586:

    I've put the relevant code out on apex.oracle.com

    Bottom line is that it seems the 'Function body returning sql' will not work if you have a page item (or variable) in your code.  Hard code the value instead and it works fine.  I've altered the code a bit, moving the query to pull the 'IN' list of names used in the pivot query from the AGENT table into a function - returning a list - which is used in the function to build and return the sql query.

    The page item P60_WG provides the 'workgroup' number - it is hard coded in the function to 'ESU' or '11' - On the form, the dropdown select would be 'ESU' and then refresh the page if you have to after page load to get it into session.

    Workspace; Richdw

    ID:  Test

    PW: Today123$

    Page 60 - 'Referrals Assigned during Period'

    If you use a hard code value on the below line: it works fine.

    select F_WGpivot(11) into v_query from dual;

    If you use

    select F_WGpivot(:P60_WG) into v_query from dual;

    it fails

    Do you have another suggestion?  I would dearly love to include a pivot report in my app - but I need to have variable columns as each workgroup has differing numbers of staff and of course they have different names.

    RIch

    · Share on TwitterShare on Facebook