created a classic report with template Badge List with pivoted data, when run the page, oracle apex returns following error which is not meaningful to end users and can be horrible for management, this error occured when my pivot query returns no data means there is "no data found", but when there is data it works fine. what i want is to display like "no data found" (or something else better way) which will be more meaningful to end user / management. please help for How To's.
report error:
ORA-30732: table contains no user-visible columns
sql statement (PL/SQL Function Body returning SQL Query):
DECLARE
V_LIST varchar2(32000);
sql_query varchar2(32000);
BEGIN
with dt as (select user_name, total_amount
from (Select user_name, sum(total_amount) total_amount
from customers_orders
where zone_id = nvl((select c001 from apex_collections where collection_name='ZONE_ADMIN'), zone_id) --NVL(:SESSION_ZONE, zone_id)
and order_date between to_date(sysdate)-7 and to_date(sysdate)
group by user_name
order by total_amount desc)
where rownum<=8),
dc as (Select distinct user_name from dt)
SELECT LISTAGG(''''||user_name||'''',',') WITHIN GROUP (ORDER BY user_name)
INTO V_LIST
FROM dc;
sql_query := 'select * from (
SELECT user_name as custname, Sum(total_amount) as totals FROM (SELECT user_name, total_amount from customers_orders where zone_id = nvl((select c001 from apex_collections where collection_name=''ZONE_ADMIN''), zone_id) and order_date between to_date(sysdate)-7 and to_date(sysdate) ) group by user_name order by 2 desc)
PIVOT (
sum(totals) for custname in ( ' ||V_LIST|| ' ))';
--apex_debug.message('Region pivot query: ', sql_query);
--:P1_NEW := sql_query;
return sql_query;
END;
regards