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