Oracle Apex Classic Report PL/SQL Function Body returning SQL Query-CodePudding

I have a dynamic SQL query that will basically add a new column for every new week of the year. The table used on the query looks something like this:

REP_NAME    WEEK
Amy 1
John    1
John    2
John    3
Nancy   1
Nick    1
Nick    2
Nick    3

In order to make the report dynamic (to add a new column per week once the table has been updated (also once per week)) I'm using the ''PL/SQL Function Body returning SQL Query'' option found on the Classic Report. For this, I previously created a function:

create or replace Function my_Testing_table_function
   RETURN varchar2
IS
v_stmt varchar2(5000);
v_my_week number;

begin
v_stmt :='with initial_data as(
select distinct
       Rep_name
  from my_testing_table
  order by 1
)' ;
for i in (select distinct week from my_testing_table ) loop
    v_stmt := v_stmt || ', week_'|| i.week || ' as(
select 
Rep_name,
count(unique_j) unique_'|| i.week ||
'
from my_testing_table
where week = ' || i.week || ' 
group by week, Rep_name
)';
end loop; 
v_stmt := v_stmt || ' select
b.Rep_name';
for i in (select distinct week from my_testing_table) loop
    v_stmt := v_stmt || ', unique_'|| i.week || ' as Week_' || i.week ;
end loop; 
    v_stmt := v_stmt || ' from initial_data b';
for i in (select distinct week from my_testing_table) loop
    v_stmt := v_stmt || ' left join week_'|| i.week || ' wk'|| i.week || ' on wk'||i.week 
||'.Rep_name = b.Rep_name';
end loop;

return v_stmt;
end;

This functions returns my desired query, however, when used on my classic report...the results are not there. Evidently I'm missing something, but have no clue what it is. Can someone advise please?

Resulting Classic Report:

CodePudding user response：

Well, yes - dynamically created report looks smart, but - from my point of view, I'd stay with a more classic approach - a simple and easily maintainable query that looks like this:

SQL> select rep_name,
  2    count(case when week = 1 then unique_j end) w1,
  3    count(case when week = 2 then unique_j end) w2,
  4    count(case when week = 3 then unique_j end) w3
  5  from my_testing_table
  6  group by rep_name
  7  order by rep_name;

REP_N         W1         W2         W3
----- ---------- ---------- ----------
Amy            1          0          0
John           1          1          1
Nancy          1          0          0
Nick           1          1          1

SQL>

Yes, it requires a little bit of copy/pasting (52 of those COUNT lines as there are 52 weeks in a year) and makes you wonder "wait a minute, does it have to look that stupid?", but - as I said, it is more than simple and everyone easily understands it (even those developers who'll some day inherit your code). Function you wrote is smart, but I wouldn't really want to debug it some day. Query I posted is trivial and easy to fix.

Now it's up to you; I'd just put my query into classic report's source.

CodePudding user response：

Just use

RETURN my_Testing_table_function();

In your example you ask APEX for return text of query as single value from DUAL.