Tree Page - Oracle Apex

Asked

Viewed 101 times

3

I’m having trouble creating a tree page on oracle Apex.

I created the page, but the Apex displays the results, but not grouped by the specified node. It is a paid bond ratio. I would like to create the Vendor node and below it the related titles.

I created a view to find 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 looked 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"

Notice I put it on start with as NOT NULL. If

oracle tree

  • start with "CODFORNEC" is NOT null.

  • Search a little bit about the with block using search breadth first. Maybe that’s what you need

1 answer

0

Sorry to post late...but here’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

Browser other questions tagged oracle tree

You are not signed in. Login or sign up in order to post.