Consider a dynamic pivot statement like the following (as described by AMIS) which
select * from table( pivot( 'select deptno, job, count(*) c from scott.emp group by deptno,job' ) )
I've used this as source SQL for classic report, selecting "Use generic column names"
The report looks like
I can parameterise the data shown with a select list, and including a where clause
where job =v(''P39_JOB'') or v(''P39_JOB'') is null
The select list does PPR on the region, demonstrated here:
https://apex.oracle.com/pls/apex/f?p=73000:39::::::
Note: , and all pivoted jobs are still visible as columns, only the data is restricted
- non-related jobs are blank
- any depts with no clerks aren't shown
Let's say you have another select list that groups jobs, say the "Pres" and "Presidents".
How could you refresh the region to exclude certain jobs?
Reloading the page with the P39_JOB value already set will not affect the outcome, while including a literal string does.
It seems perhaps ODCI function parses the query on null binds, then populates with actual results.
Does anyone have any suggestions?
update: it seems one workaround is to inject the value as a substitution string, eg:
and job like '&P39_CEO.'
where P39_CEO defaulted in session state to %
I only got this concept working in my client app (4.2, 11gr2), not my demo (5.0, 12c), even attempting to cater for relevant quotes and when null. I kept getting
report error: ORA-1403: no data found
I also couldn't work out how to properly apply sys.dbms_assert.enquote_literal()
Scott