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