-- query selects 3 columns, the link, the label, and the value
--
select decode(:P9_COUNTRY,'0',
            'f?p=4600:32:'||:app_session||':::RIR:IR_DNAME:'||dname,
            'f?p=4600:32:'||:app_session||':::RIR:IR_DNAME,IR_COUNTRY:'||
            dname||','||:P9_COUNTRY )
            click_link,
          dname chart_label,
          count(*) chart_value
from WWV_DEMO_EMP e,
     wwv_demo_dept d
where e.deptno = d.deptno and
          (nvl(:P9_COUNTRY,'0') = '0' or work_country = :P9_COUNTRY)
group by dname