using Apex 21.1 database 21cXE
for one of my query results, i have used a package (downloaded from GitHub) named Method4 which taking a query statement and output results are pivoted.
link: https://github.com/method5/method4
i have set an application item value after successful login as below:
apex_util.set_session_state(p_name => 'SESSION_ZONE',
p_value => l_zoneid);
when using :SESSION_ZONE in a sql statement it is showing error:
ORA-01008: not all variables bound
below my query (without zone_id in where clause it is showing results fine), also used with single quotes but received error from apex.
select *
from table(method4.pivot(
q'[
select pivot_column_id, u_name, to_char(value, 'fm999,999,999')
from (select sum(o.order_total) pivot_column_id, initcap(u.user_name) u_name, sum(o.rider_charges) value
from orders o, users u
where o.rider_id = u.id and order_date between to_date(sysdate)-7 and to_date(sysdate)
and o.zone_id = nvl(:session_zone, o.zone_id) -- ZONE_ID is here...
group by o.user_id, u.user_name
order by 1 desc)
where rownum<=8
]'
));
note: when remark o.zone_id line in where clause, showing results but i want to add zone_id check as per requirement, please help. ( or any other way i can have pivoted data like --for CR with Badge List template-- ):
CLARK ADAM JOHN
1000 1500 1200
have also tried below (in sql command window) it is showing: ORA-00936: missing expression
select * from (
select initcap(u.user_name) uname, sum(o.rider_charges) uvalue
from orders o, users u
where o.rider_id = u.id
and order_date between to_date(sysdate)-7 and to_date(sysdate)
and nvl(o.rider_charges,0) > 0
group by initcap(u.user_name)
)
pivot(
sum(uvalue)
for (uname) in (Select user_name from users)
)
regards