Show multiple display values on interactive report column with multiple values based on LOV (checklist)

Question

I want to show the display values in the column, but the column is a string with entries like '1:3', '2', '1:2:4', which are the return values based on a LOV with static values.

If I set the column as "Display text (Based on LOV)" it will only work for those entries with one number only, like '3' or '1'.

I thought of normalizing the table, but then I would have a multi table form. This should be simple, yet I cannot figure out how to do this?

    select p.COD,
          UNIDADE_PROPONENTE,
          CAUSA_FUND,
          CAUSAS_RELAC,
          TITULO,
          DESC_SOLUC,
          listagg(a.acao, ';<br><br>') within group (order by null)  as "a.acao",    
          OUTRA_ACAO,
          SUGESTAO,
          SITUACAO
      from TB_PROPOSTA p, TB_ACAO a,
      table(cast(multiset(select level from dual
                            connect by level <=regexp_count(p.acao,':') + 1)
                            as sys.odcinumberlist)) x

      where 
        a.cod = regexp_substr(p.acao, '[^:]+', 1, x.column_value)
        and
        (:P2_XUNIDADE_RESPONSAVEL = UNIDADE_PROPONENTE OR :P2_XUNIDADE_RESPONSAVEL IS NULL)
        and   
        (INSTR(':'|| p.acao ||':', ':' || :P2_XACAO || ':') > 0 OR :P2_XACAO IS NULL)
    group by p.cod, p.acao, unidade_proponente, causa_fund, causas_relac, titulo, desc_soluc, outra_acao, sugestao, situacao
    order by p.cod, p.acao;

edit: Now I either get missing keyword error or sql command not properly ended. This one gives SQL COMMAND NOT PROPERLY ENDED

select p.COD,
       UNIDADE_PROPONENTE,
       CAUSA_FUND,
       CAUSAS_RELAC,
       TITULO,
       DESC_SOLUC,  
       listagg(a.acao, ';<br><br>') within group (order by null) as "a.acao",    
       OUTRA_ACAO,
       SUGESTAO,
       SITUACAO
  from TB_PROPOSTA p
  cross join table(cast(multiset(select level from dual
                        connect by level <=regexp_count(p.acao,':') + 1)
                        as sys.odcinumberlist)) x
  left join TB_ACAO.ACAO  

on TB_ACAO.COD = regexp_substr(TB_PROPOSTA.ACAO, '[^:]+', 1, x.column_value) 

    where
        (:P2_XUNIDADE_RESPONSAVEL )= UNIDADE_PROPONENTE OR :P2_XUNIDADE_RESPONSAVEL IS NULL)
        AND
        (:P2_XACAO IN (p.ACAO) OR :P2_XACAO IS NULL)
    group by p.cod, p.acao, unidade_proponente, causa_fund, causas_relac, titulo, desc_soluc, outra_acao, sugestao, situacao
    order by p.cod, p.acao;

And if I change the "cross join" to only "join" I get the "Missing keyword (ORA-06550: line 17, column 1: ORA-00905: missing keyword) right before the "where"

I'm sorry, I don't quite understand. Are display and return values the same? That's what your first sentence suggests. Why would normalization cause a multi table (?) form? Which Apex version do you use? Could you, by any chance, create an example on apex.oracle.com and let us know the credentials so that we'd see it? Or, at lest, post a screenshot? I really have difficulties in imagining what you've described, sorry. — Littlefoot
Sure. It is APEX 5.0.2. About the normalization issue, how would I make the form act on the intersection table without a multi table form? Here is the link. The column in question is "Tipo acao". The display values aren't the same as return values. So, I want it to display something like "This is display value 1; This is display value 2" instead of "1:2". — V. M.

Littlefoot Littlefoot · Accepted Answer · 2018-01-19T21:51:53

Thank you for additional explanation.

Would something like this help? My TEST table acts as your report query, while the rest of it splits colon separated values. It is a SQL*Plus example, just to show you what's going on.

SQL> with test (situacao, tipo_acao) as
  2  (select 'Aguardando resposta', '1:2:3' from dual union
  3   select 'Aguardando resposta', '5' from dual
  4  )
  5  select
  6    situacao,
  7    'This is value ' || regexp_substr(tipo_acao, '[^:]+', 1, column_value) tipo_acao
  8  from test,
  9    table(cast(multiset(select level from dual
 10                        connect by level <= regexp_count(tipo_acao, ':') + 1)
 11                        as sys.odcinumberlist));

SITUACAO            TIPO_ACAO
------------------- ----------------------------------
Aguardando resposta This is value 1
Aguardando resposta This is value 2
Aguardando resposta This is value 3
Aguardando resposta This is value 5

SQL>

[EDIT, after creating Page 5 in your Apex application]

I think I got it - have a look at Page 5 (created as copy of your page 2 so that I wouldn't spoil it). Its column list is reduced, but you'll get the general idea. Query looks like this; I hope it's OK:

select p.cod,
       s.situacao,
       p.tipo_acao,
       listagg(t.acao, ', ') within group (order by null) acao
from tb_proposta p,
     tipo_situacao s,
     vw_unid v,
     tipo_acao t,
     table(cast(multiset(select level from dual
                         connect by level <= regexp_count(p.tipo_acao, ':') + 1)
                         as sys.odcinumberlist)) x
where s.cod = p.proposta_status
  and p.cod_vw_unid = v.cod
  and t.cod= regexp_substr(p.tipo_acao, '[^:]+', 1, x.column_value)
group by p.cod, s.situacao, p.tipo_acao
order by p.cod, p.tipo_acao;

[EDIT #2, ANSI outer join]

This is how you should try to do it. Also, CROSS JOIN and its place in query.

select p.cod,
       s.situacao,
       p.tipo_acao,
       listagg(t.acao, ', ') within group (order by null) acao
from tb_proposta p
cross join table(cast(multiset(select level from dual
                               connect by level <= regexp_count(p.tipo_acao, ':') + 1)
                               as sys.odcinumberlist)) x 
      join tipo_situacao s   on s.cod = p.proposta_status
      join vw_unid v         on v.cod = p.cod_vw_unid
left  join tipo_acao t  on t.cod = regexp_substr(p.tipo_acao, '[^:]+', 1, x.column_value)
group by p.cod, s.situacao, p.tipo_acao
order by p.cod, p.tipo_acao;

Show multiple display values on interactive report column with multiple values based on LOV (checklist)

1 Answers