This discussion is archived
9 Replies Latest reply: Jan 24, 2013 3:51 PM by Mahmoud_Rabie RSS

Question regarding Trees in APEX

John K. Newbie
Currently Being Moderated
Hi All - I'm struggling with learning how to use trees in APEX and would appreciate any assistance.

The issue may be that my data is basically text and my table columns don't have primary keys etc. Is this a requirement in order to build Trees in Apex?

Here is my table:
CREATE TABLE GSE_LANGUAGE_TABLES
  (
    FAMILY        VARCHAR2(200 CHAR) ,
    NAME          VARCHAR2(200 CHAR) ,
    TABLE_NAME    VARCHAR2(30) ,
    NUM_NONSEEDED NUMBER
  );


Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('CRM','Enterprise Contracts','OKC_K_LINES_TL',459);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('CRM','Enterprise Contracts','OKC_XPRT_QUESTIONS_TL',81);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('CRM','Multichannel Technologies','MCT_CFG_TOKENS_TL',0);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('CRM','Multichannel Technologies','MCT_CFG_CONN_TL',0);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('CRM','Multichannel Technologies','MCT_CFG_TASK_FLOW_PARAMS_TL',0);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('CRM','Sales Prediction Engine','ZSP_BR_ATTRIBUTES_TL',0);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('CRM','Trading Community Hub','ZCH_DEDUP_LINKS_TL',0);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('CRM','Trading Community Hub','ZCH_DUP_IDNT_EXCLUSIONS_TL',0);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('CRM','Trading Community Model','HZ_STYLE_FMT_LAYOUTS_TL',0);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('CRM','Trading Community Model','HZ_PARTY_USAGES_TL',0);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('Financials','Assets','FA_ADDITIONS_TL',2493);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('Financials','Payments','IBY_FNDCPT_USER_CC_PF_TL',0);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('Financials','Receivables','AR_BPA_TEMPLATES_TL',0);
Insert into GSE_LANGUAGE_TABLES (FAMILY,NAME,TABLE_NAME,NUM_NONSEEDED) values ('Financials','Receivables','AR_AGING_BUCKET_LINES_TL',9);

COMMIT;
The Tree that I'm looking for would look like this when I expand the Family node and a select Name node:
CRM
   Enterprise Contracts
   Multichannel Technologies
   Sales Prediction Engine
   Trading Community Hub
        ZCH_DEDUP_LINKS_TL
        ZCH_DUP_IDNT_EXCLUSIONS_TL   etc etc....    
The code that Apex generated when I used the Wizard is this,
select case when connect_by_isleaf = 1 then 0
            when level = 1             then 1
            else                           -1
       end as status, 
       LEVEL, 
       "FAMILY" as title, 
       null as icon, 
       "NAME" AS VALUE, 
       "TABLE_NAME" as tooltip, 
       null as link 
from GSE_LANGUAGE_TABLES
start with "FAMILY" = 'CRM'
CONNECT BY PRIOR "FAMILY" = "NAME"
ORDER SIBLINGS BY "TABLE_NAME";
The result is just one family value repeated over and over again and there is no tree per se as these all appear as lowest level values:
-CRM
-CRM
-CRM etc..
Obviously I'm new to this so any advice on the proper code to achieve the desired result would be much appreciated. I've reviewed the APEX Manual and the info on Trees is pretty sparse so I'm not clear on what I need to enter for ID, PARENT ID, NODE TEXT, START WITH, START TREE etc.

Thanks in advance,
John

Edited by: user703358 on Jan 24, 2013 12:31 PM

Edited by: user703358 on Jan 24, 2013 12:33 PM
  • 1. Re: Question regarding Trees in APEX
    TexasApexDeveloper Guru
    Currently Being Moderated
    A tree needs a primary key to see the RELATIONSHIP between the elements.. Thus, how do you know what items is a child/subordinate of a parent leaf?

    Thank you,

    Tony Miller
    Ruckersville, VA
  • 2. Re: Question regarding Trees in APEX
    John K. Newbie
    Currently Being Moderated
    Thanks. In my data, the table_name is a unique value so I'm assuming that can function as the key. Given that, I'm still not sure how to structure my code.

    thanks,
    john
  • 3. Re: Question regarding Trees in APEX
    Sc0tt Expert
    Currently Being Moderated
    I'd structure your data a bit different. You need a clear child/parent relationship which you don't have. If you are willing to restructure a bit more like this, it should work. Note this is an example, but check it out. Thank you for posting a script too! Almost no one does that these days.
    create table simple_rel
    (name   varchar2(200),
     seed   number,
     parent varchar(200));
    
    
    insert into simple_rel values('CRM',null, null);
    insert into simple_rel values('Financials', null, null);
    insert into simple_rel values('Enterprise Contacts', null,'CRM');
    insert into simple_rel values('Trading Hub', null,'Financials');
    insert into simple_rel values('Table 1',234, 'Enterprise Contacts');
    insert into simple_rel values('Table 2',23334, 'Enterprise Contacts');
    insert into simple_rel values('Table 1',67, 'Trading Hub');
    insert into simple_rel values('Table 1',99, 'Trading Hub');
    
    select name,
           level
      from simple_rel
     connect by parent = prior name
     start with parent is null
    Oops - had trading hub going to CRM instead of financials
  • 4. Re: Question regarding Trees in APEX
    Mahmoud_Rabie Journeyer
    Currently Being Moderated
    Dear user703358

    I would break your big table to three tables as the following
    CREATE TABLE GSE_FAMILIES
      (
        FID             NUMBER,
        FAMILY        VARCHAR2(200 CHAR) ,
        CONSTRAINT pk_fid PRIMARY KEY (FID)
      );
    
    CREATE TABLE GSE_NAMES
      (
        NID             NUMBER,
        FID              NUMBER,
        NAME          VARCHAR2(200 CHAR) ,
        CONSTRAINT pk_nid PRIMARY KEY (NID),
        CONSTRAINT fk_fid
                   FOREIGN KEY (FID)
                   REFERENCES GSE_FAMILIES(FID)
      );
    
    CREATE TABLE GSE_TABLES
      (
        TID             NUMBER,
        NID             NUMBER,
        TABLE_NAME    VARCHAR2(30) ,
        CONSTRAINT pk_tid PRIMARY KEY (TID),
        CONSTRAINT fk_nid
                   FOREIGN KEY (NID)
                   REFERENCES GSE_NAMES(NID)
      );
    and the sample inserts as following
    insert into GSE_FAMILIES (FID,FAMILY) values (1,'CRM');
    insert into GSE_FAMILIES (FID,FAMILY) values (2,'Financials');
    
    insert into GSE_NAMES (NID,FID,NAME) values (1,1,'Enterprise Contracts');
    insert into GSE_NAMES (NID,FID,NAME) values (2,1,'Multichannel Technologies');
    insert into GSE_NAMES (NID,FID,NAME) values (3,1,'Sales Prediction Engine');
    insert into GSE_NAMES (NID,FID,NAME) values (4,1,'Trading Community Hub');
    insert into GSE_NAMES (NID,FID,NAME) values (5,2,'Assets');
    insert into GSE_NAMES (NID,FID,NAME) values (6,2,'Payments');
    insert into GSE_NAMES (NID,FID,NAME) values (7,2,'Receivables');
    
    insert into GSE_NAMES (TID,NID,NAME) values (1,1,'OKC_XPRT_QUESTIONS_TL');
    insert into GSE_NAMES (TID,NID,NAME) values (2,2,'MCT_CFG_TOKENS_TL');
    insert into GSE_NAMES (TID,NID,NAME) values (3,2,'MCT_CFG_CONN_TL');
    insert into GSE_NAMES (TID,NID,NAME) values (4,2,'MCT_CFG_TASK_FLOW_PARAMS_TL');
    ....
    .....
    I will assume the total count of any table doesn't exceed 1000
    Now, we will make a view connecting the parents with children
    CREATE OR REPLACE FORCE VIEW  "GSE_VW" ("ID", "PID", "NODE") AS 
      select    FID as ID,
                  null as PID ,
                  FAMILY as NODE
      from      GSE_FAMILIES
      union
      select   NID+1000 as ID,
                 FID as PID,
                 NAME as NODE
      from     GSE_NAMES
      union
      select   TID+2000 as ID,
                 NID+1000 as PID,
                 TABLE_NAME as NODE
      from     GSE_TABLES
    /
    ‚Äč
    Now let's write this code in the tree region
    select case when connect_by_isleaf = 1 then 0
                    when level = 1             then 1
                    else                           -1
                    end as status, 
           level, 
           NODE as title, 
           null as icon, 
           ID as value, 
           null as tooltip, 
           null as link 
    from GSE_VW
    start with PID is null
    connect by prior ID = PID
    order siblings by ID
    Another way is done here
    http://apex.oracle.com/pls/apex/f?p=36648:13:3839453945489:::::
    To log in to this sample application, please use guest / apex_chart_demo.

    If this solves the problem, please mark it as Correct. Otherwise, mark it as helpful

    Best Regards
    Mahmoud
  • 5. Re: Question regarding Trees in APEX
    John K. Newbie
    Currently Being Moderated
    Thanks Scott that helps a lot.

    I think I see how I need to structure my data but I'll need to work with this a bit. I was able to get the exact results I want using your example, although the exact syntax didn't work so I had to let the Wizard come up with the code.

    I'm not that strong in SQL (obviously) so even though this works perfect it doesn't make any sense to me:
    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, 
           "NAME" as value, 
           "SEED" as tooltip, 
           null as link 
    from "#OWNER#"."SIMPLE_REL"
    start with parent is null
    connect by parent = prior "NAME"
  • 6. Re: Question regarding Trees in APEX
    Sc0tt Expert
    Currently Being Moderated
    Glad it helped. Basically it breaks down like this:

    connect by signifies how to traverse the tree. In this case we have parent = prior "NAME"
    Name        Parent
    -------------------------------------
    CRM
    Contacts    CRM
    Table1      Contacts
    Table2      Contacts
    Basically it's a zigzag. For each row, the parent is equal to the prior row's value in the "name" column. Depending on which side of the equal sign the word prior appears, it determines whether you go up or down the hierarchy.

    Start with tells you where to start on the tree. If you say start with parent is null, your "top" level is everything without a parent, or your top branches.
  • 7. Re: Question regarding Trees in APEX
    John K. Newbie
    Currently Being Moderated
    Ok got it - thanks so much.

    Thank you Mahmoud for the detailed explanation as well. I have a lot to work with now.

    Very much appreciated all!

    cheers,
    john
  • 8. Re: Question regarding Trees in APEX
    Sc0tt Expert
    Currently Being Moderated
    Glad it helped! The key will be to restructure it a bit for a parent/child relationship.

    Feel free to open a new thread if you get stuck.
  • 9. Re: Question regarding Trees in APEX
    Mahmoud_Rabie Journeyer
    Currently Being Moderated
    Welcome any time

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points