oracle apex IR with link column with listagg values to send single value

ragu_s

Hello,

I am trying to send single value on '|' separated link column, any inputs will be helpful. I have setup an example within my personal workspace.

So, for example in my report if I click on first accounting row for employees(s) column on hello1, it should only pick this value not the whole 'ADAMS|HELLO1|JACKIE|JONES|King|WARD'.

I am using below sql for report query and made employee(s) as link column to redirect to modal dialog.

Workspace Details:

workspace: raghu_workspace

username/password: test/test12345

App# 25244, Pg# 1 & 2.

SELECT

  t1.dname,

  LISTAGG(

    t2.ename,

    '|'

  ) WITHIN GROUP(

  ORDER BY

    t2.ename

  ) AS employees

FROM

  dept t1, emp t2

  where t1.deptno = t2.deptno

GROUP BY

  t1.dname

ORDER BY

  t1.dname;

Thanks,

Ragu

jariola

I did add page 556 to your example app using this query

with qry as(
  select t1.dname
    ,'<a href="'
    ||apex_page.get_url(
      p_page => 2
      ,p_items  => 'P2_ENAME'
      ,p_values => t2.ename
    ) 
    || '">'
    || t2.ename
    || '</a>'
    as ename
  from dept t1
  join emp t2 on t1.deptno = t2.deptno
)
select
  qry.dname
  ,listagg(
    qry.ename
    ,'|'
  ) within group(
  order by
    qry.ename
  ) as employees
from qry
group by
  qry.dname
order by
  qry.dname

ragu_s

Thank you, perfect.

ragu_s

Thanks Jari, I tried to apply the same to my query and I am getting

report error: ORA-01489: result of string concatenation is too long

I know it's because of my columns length. Is there an alternative option to achieve this same functionality.

And again thank you for your support.

-Ragu

Pierre Yotti

Hi Ragu,

please take a look here: https://www.pipperr.de/dokuwiki/doku.php?id=prog:sql_liste_erstellen

http://www.grassroots-oracle.com/2015/03/listagg-to-clob-avoid-4000-chr-limit.html

Regards

Pierre