Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Unhappy Unanswered: Tree view question

    Hi all

    suppose that I have this table

    emp
    -----------
    no ---> employee number
    name ---> employee name
    salary ---> employee salary
    emp_no ---> employee manager number, its foregin key from the same table

    I tried to view this table as tree, I used to do this using DELPHI it was very easy, just specifing the key and the parent, but in developer 6i I can't

    how I can do this????

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    By using the CONNECT BY clause in the SELECT statement.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Angry

    I trid to write this select

    select name from emp connect by no = emp_no order by no

    I used (Populate_Tree) function also

    but it's not working

    any idea ?!??!?!?!?!?!?!?

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    You would want something more in the style of:

    Code:
    select name 
    from emp
    connect by no = prior empno
    start with empno is null
    Play with that - you need the prior keyword in there for it to work the way you want it.
    Oracle OCPI (Certified Practicing Idiot)

  5. #5
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Angry

    I tried the code above

    still not working ???????

    the error message is (data used to populate the tree is invalid) FRM-74321

    any other RIGHT idea

  6. #6
    Join Date
    Jan 2004
    Posts
    492

    Cool

    Quote Originally Posted by giggs11
    any other RIGHT idea
    First off curb the attitude - YOU are asking US for help!! If you were so smart you would have figured it out yourself! No one can see your screen nor are we mind readers.

    I get confused sometimes at which direction the hierarchical clause works - so in the query above, try switching the no and empno to different sides. The query below will start with a manager, and go down the chain by employees. Note in my query I *assume* that the top manager does not have a manager. You will have to adjust the START WITH clause to suit your data. Usually in the Oracle packaged systems the top level in the hierarchy does not get populated with a parent.

    Code:
     
    
    select *
    from emp
    connect by empno = prior no
    start with empno is null
    Run that outside of Forms, and see what you pull back for data. I can't vouch for the Forms error, but I can tell you that is the style of a hierarchical query. I am waiting with baited breath on your results
    Last edited by ss659; 11-08-04 at 17:08.
    Oracle OCPI (Certified Practicing Idiot)

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    I'm bumping this to the top
    Oracle OCPI (Certified Practicing Idiot)

  8. #8
    Join Date
    Apr 2004
    Posts
    246
    not sure, but I believe the question is "how do I make a Hierarchical Tree in Oracle Forms. From Metalink (hence poorly formatted):

    Example: Creating a Simple Tree
    ===============================
    Create a table (MyTreeData) with the following columns:
    Initial_level NUMBER
    Node_Depth NUMBER
    Node_label VARCHAR2(10)
    Node_icon VARCHAR2(30)
    Node_data VARCHAR2(40)
    Node_parent VARCHAR2(10)
    Populate the table with the tree data. For example:
    Initial_ Node_ Node_ Node_ Node_ Node_
    level Depth label icon data parent
    -1 2 Car Null CAR TRANSPORT
    -1 2 Plane Null PLANE TRANSPORT
    0 2 Bike NULL BIKE TRANSPORT
    1 1 Transport NULL TRANSPORT
    0 3 Honda NULL HONDA CAR
    0 3 Boeing 747 NULL 747-400 PLANE
    0 3 Mazda NULL MAZDA CAR
    Create a form as follows:
    1) Create a Default block and layout, based on MyTreeData.
    2) Create a new non base table block (MyTreeBlock).
    3) Create a hierarchical tree in this block using the 'Hierarchical
    Tree' icon on the Tool Palette of the layout editor.
    4) Open the property palette of the created Hierarchical Tree.
    5 Change the 'Name' to 'MYTREE'.
    6) ADD the following query to the 'Data Query' property:
    SELECT initial_level
    ,node_depth
    ,node_label
    ,node_icon
    ,node_data
    FROM MyTreeData
    START WITH node_parent IS NULL
    CONNECT BY node_parent = prior node_data
    7) Create a button in the MyTreeData (ie in the first block).
    8) Add a WHEN_BUTTON_PRESSED trigger to the button with the following code:
    DECLARE
    htree ITEM;
    top_node FTREE.NODE;
    find_node FTREE.NODE;
    BEGIN
    -- Find the tree itself.
    htree := FIND_ITEM('mytreeblock.mytree');
    -- Populate the tree with data.
    ftree.Populate_Tree(htree);
    END;
    9) Run the form.
    10) Press the Button to display the tree.
    11) Query the MyTreeData block.
    12) Try adjusting the values of INITIAL_LEVEL (1 and -1), add new
    records and so forth.
    13) Save the changes and press the button to display your changes.
    References
    ==========
    See <Note 187733.1> "Using Icons in Hierarchical Tree" for information on
    using icons with a tree structur
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  9. #9
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Red face

    dear ss659:

    first of all I am sorry for my tough words,
    I thank u verymuch because u r nice person, I talk to you in bad way and u try to help me again


    I trid to switch the no and empno but still not working, ist need any thing more than specifiy the query to the tree and apply the populate_tree?

    thanx in advance

  10. #10
    Join Date
    Apr 2004
    Posts
    246
    You have two issues. 1 - get the sql to work, 2 - get forms to work.
    Let's work on the first issue first. Only you know your data, not us. So you tell us, what defines a top level person (empno is null, = 0, = -99, what?). Then change the query to handle it:
    select *
    from emp
    connect by empno = prior no
    start with empno is null
    ( or start with empno = -999 or whatever you need)

    Run that query in sqlplus, changing the start with clause until you get data.

    Once you get data, read my previous post of how to use a tree query in Forms.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  11. #11
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Angry

    I trid all the steps above >>>> nothing work

    I got sick and tired of Delevloper<, I reverence oracle database its the best
    but the developer is pool of ...

    10 years ago I was using DELPHI 3, I used the tree view (which is new feature in developer 6i) its take me 10 sec to solve it, just assign the table, the key field, and the parent key then every thing is working without need any help.

    I am not genius, but I am not stupid also, its very simple case but the developer is the stupid.

    anyway if anyone has any idea plz send it

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by giggs11
    I got sick and tired of Delevloper<, I reverence oracle database its the best
    but the developer is pool of ...

    10 years ago I was using DELPHI 3, I used the tree view (which is new feature in developer 6i) its take me 10 sec to solve it, ... blah, blah, whinge, whine, ...

    Well Developer 6i is several years old now, so I am not sure what your point is about the tree being a new feature.

    Calm down: it really is quite simple, although the Developer on-line help could be better.

    Follow shoblock's instructions above, using an appropriate query - which must have 5 columns as in the standard EMP example here:
    Code:
    select -1 init_state, level, ename||' '||sal label, '' icon, empno data
    from emp
    connect by mgr = prior empno
    start with mgr is null
    I just created a simple form myself and implemented that tree - it works!

  13. #13
    Join Date
    Apr 2004
    Posts
    246
    well I'm glad somebody got it to work (I never actually implemented it myself, I just happen know where the instructions are - the forms on-line help really is totally useless for trees).
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  14. #14
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Talking

    finally it works

    thank u all .... thank u Andrews

    by the way I have hundreds of threads saved on my pc which replied by Andrews, I save them as reference


    thank u again and i am sorry about my anger.....
    my family call me DR.Beacker cause of my anger

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •