Skip to Main Content

APEX

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
191 views