APEX

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".

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

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

Any ideas what am I missing?

Thanks in advance