Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

APEX 5.0 : dynamic pivot 'in' clause returning no data

mikeTelJul 12 2018 — edited Jul 12 2018

Hello Gurus,

I have the following dynamic query for classic report based on function.

DECLARE

l_sql varchar2(32767);

l_sql2 varchar2(32767);

l_query varchar2(32767);

l_count pls_integer := 0;

l_count2 pls_integer := 0;

l_pivot_column varchar2(32767);

l_column_no pls_integer := 1;

BEGIN

l_sql := 'WITH DGRID AS (';

--

l_sql := l_sql || ' ' || 'select distinct';

l_sql := l_sql || ' ' || ',lead_name';

l_sql := l_sql || ' ' || ',seq';

l_sql := l_sql || ' ' || ',count(lead_name) over () fcount';

l_sql := l_sql || ' ' || 'from ';

l_sql := l_sql || ' ' || 'from tablA';

l_sql := l_sql || ' ' || 'where trim(lead_name) is not null';

l_sql := l_sql || ' ' || 'and group_id = :P7_CURRENT_GROUP';

l_sql := l_sql || ' ' || ')';

--

l_sql := l_sql || ' ' || 'SELECT * FROM dgrid';

l_sql := l_sql || ' ' || 'PIVOT';

l_sql := l_sql || ' ' || '(';

l_sql := l_sql || ' ' || 'max(fcount) FOR lead_name in (%PTL%))';

l\_sql:=replace(l\_sql,'%PTL%',:P7\_ITEM\_NAMES);

return l\_sql;

END;

When running it, it gives an error saying "report error : no data found".

Screen Shot 2018-07-12 at 17.21.34.png

I ran it in debug mode and found the following error:

Screen Shot 2018-07-12 at 17.23.05.png

But when I tried to ran it using generic columns, there is no issue.

Any ideas what am I missing?

Thanks in advance

This post has been answered by fac586 on Jul 12 2018
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 9 2018
Added on Jul 12 2018
4 comments
276 views