Tree Page - Oracle Apex

3

I'm having trouble creating a tree page on oracle apex .

I created the page, but apex displays the results, but not grouped by the specified node. It's a list of paid titles. I would like to create the Vendor node and below it the related titles.

I created a view to fetch the titles:

CREATE OR REPLACE VIEW V_TIT_FORN AS 
SELECT P.NOME, T.CODIGO, T.CODFORNEC, T.PARCELA, T.DTVENCTO, T.VLRPARCELA, T.VLRPAGO
  FROM PESSOA P
  ,    TITULO T
 WHERE P.CODIGO = T.CODFORNEC
   AND T.TPTIT = 0;

And the page creation select looks like this:

select case when connect_by_isleaf = 1 then 0
            when level = 1             then 1
            else                           -1
       end as status, 
       level, 
       "NOME" as title, 
       null as icon, 
       "CODIGO" as value, 
       null as tooltip, 
       null as link 
from "#OWNER#"."V_TIT_FORN"
where DTVENCTO > TO_DATE('01/03/2016','DD/MM/RRRR')
start with "CODFORNEC" is NOT null
connect by prior "CODIGO" = "CODFORNEC"
order siblings by "NOME"

Note that I put start with as NOT NULL . If

    
asked by anonymous 14.06.2016 / 15:30

1 answer

0

Sorry for late posting ... but that's how it worked.

select case
      when connect_by_isleaf = 1 then
       0
      when level = 1 then
       1
      else
       -1
   end as status
  ,level
  ,name as title
  ,null as icon
  ,id as value
  ,null as tooltip
  ,decode(level,4,'f?p=&APP_ID.:34:'||:APP_SESSION||':::RP,34:P34_CODIGO,P34_PARCELA:'||titulo||','||parcela,null) as link 
from (
    select to_char(p.codigo) id,
           to_char(null)     parent,
           p.NOME            name,
           0                 titulo,
           0                 parcela
      from pessoa p,
           titulo  t
     where p.codigo = t.codFornec
       and t.vlrpago = 0

    UNION

    select distinct t.codFornec || ':' || to_char(t.dtvencto,'dd/mm/rrrr') id, 
           to_char(t.codFornec)                                            parent ,
           'Data: ' || to_char(t.dtvencto,'dd/mm/rrrr')                    name,
           0                 titulo,
           0                parcela
      from titulo t
     where t.vlrpago = 0

    UNION

    select t.codFornec || ':' || to_char(t.dtvencto,'dd/mm/rrrr') || ':' || t.codigo id, 
           to_char(t.codFornec) || ':' || to_char(t.dtvencto,'dd/mm/rrrr')           parent ,
           'A.P.: '|| t.codigo                                                       name,
           0                 titulo,
           0                 parcela
      from titulo t
     where t.vlrpago = 0

    UNION

    select t.codFornec || ':' || to_char(t.dtvencto,'dd/mm/rrrr') || ':' || t.codigo ||':'|| t.parcela id, 
           to_char(t.codFornec) || ':' || to_char(t.dtvencto,'dd/mm/rrrr') || ':' || t.codigo          parent ,
           'Parcela: '|| t.parcela ||' - '|| to_char(t.vlrParcela,'999G999G999G999G990D00')            name,
           t.codigo          titulo,
           t.parcela         parcela
      from titulo t
     where t.vlrpago = 0

    )
start with parent is null
connect by parent = prior id
order siblings by name
    
23.02.2018 / 15:32