This discussion is archived
10 Replies Latest reply: Dec 16, 2012 5:14 AM by Andreas Weiden RSS

creating a simple  tree based on a query

newbi_egy Explorer
Currently Being Moderated
db and dev 10g rel2 , xp sp3

hi all ,
i am trying to create a simple tree (tree5) to get the data of "test_name" column of "tests" table , i am using this code in a pre_form trigger :

ftree.set_tree_property('tree5',ftree.query_text,'select test_name from tests') ;
ftree.populate_tree('tree5') ;

but it is not working "invalid query for the hierarchical tree" ???
thanks
  • 1. Re: creating a simple  tree based on a query
    Andreas Weiden Guru
    Currently Being Moderated
    the query for a tree has to have certain columns. Have a look at the onlinehelp or read this http://andreas.weiden.orcl.over-blog.de/article-29307730.html
  • 2. Re: creating a simple  tree based on a query
    newbi_egy Explorer
    Currently Being Moderated
    i've tried it like so ,and it failed too .

    ftree.set_tree_property('tree5',ftree.query_text,'select 0 , level , test_name , null , test_id from tests') ;
    ftree.populate_tree('tree5') ;

    i just want to build a simple tree , with no parents or childs nodes

    thanks
  • 3. Re: creating a simple  tree based on a query
    newbi_egy Explorer
    Currently Being Moderated
    i've looked at my build internet application book , and he just provided me an example :

    DECLARE
    htree ITEM;
    v_ignore NUMBER;
    rg_emps RECORDGROUP;
    BEGIN
    htree := Find_Item('tree_block.htree3');
    rg_emps := Create_Group_From_Query('rg_emps',
    'select 1, level, last_name, NULL, to_char(employee_id) '
    ||' from employees ' ||
    'connect by prior employee_id = manager_id ' ||
    'start with job_id = ''AD_PRES''');
    v_ignore := Populate_Group(rg_emps);
    Ftree.Set_Tree_Property(htree, Ftree.RECORD_GROUP,rg_emps);
    END;

    and it is working , but
    when i connect with "scott/tiger" , and change all the columns mentioned to columns' names of the scott/tiger schema , it does not work , i do not know why ?
    DECLARE
    htree ITEM;
    v_ignore NUMBER;
    rg_emps RECORDGROUP;
    BEGIN
    htree := Find_Item('block6.tree4');
    rg_emps := Create_Group_From_Query('rg_emps',
    'select 1, level, ename, NULL, to_char(empno) '
    ||' from emp ' ||
    'connect by prior empno = mgr ' ||
    'start with job_id = ''CLERK''');
    v_ignore := Populate_Group(rg_emps);
    Ftree.Set_Tree_Property(htree, Ftree.RECORD_GROUP,rg_emps);
    END;

    then these errors appears :
    1- can not create record group , -- why ? i think there is nothing wrong with my syntax .
    2- the specified tree data source is not a recordgroup .

    and the book does not demonstrate the lines :
    'connect by prior employee_id = manager_id ' ||
    'start with job_id = ''AD_PRES''');

    i do not know , for what it is written ?

    hope this can clear my problem
    and hope you can provide me with a link to the online help you've mentioned .
    thanks

    Edited by: semsem on Dec 14, 2012 10:10 AM
  • 4. Re: creating a simple  tree based on a query
    Andreas Weiden Guru
    Currently Being Moderated
    i think there is nothing wrong with my syntax
    Did you test it? Does the query run in SQL*Plus?
    i do not know , for what it is written ?
    Did you try google? Did you try the oracle documentation?
    and hope you can provide me with a link to the online help you've mentioned
    I mean the onlinehelp of forms. Try F1.
  • 5. Re: creating a simple  tree based on a query
    newbi_egy Explorer
    Currently Being Moderated
    Did you test it? Does the query run in SQL*Plus?
    what part do you mean ?
    Did you try google? Did you try the oracle documentation?
    yes , sure i've tried , and if i found what i need , i would not have asked in the forum ,
    all examples are alittle bit complex , and have things i do not need at all , like the "connect prior" line and "start with" line .
    I mean the onlinehelp of forms. Try F1.
    the examples in the onlinehelp also uses lines and built-ins like i said before "connect prior and start with" , which
    i do not understand .

    the last thing andreas , i really appreciate your help , but you've posted about three posts in this thread without a vain ,
    and you could easily answer my simple question directly to accomplish my prompt task .

    - i've "tests" table (test_id,test_name) , and i need a tree to view the "test_name" column .
    i do not need it to be expandable , or anything just this .

    thanks man
  • 6. Re: creating a simple  tree based on a query
    Andreas Weiden Guru
    Currently Being Moderated
    Did you test it? Does the query run in SQL*Plus?
    what part do you mean ?
    I mean the query you use in your creation of the record-group. If you say it does not work, it seems likely that your query is not correct, so test it in SQL*Plus or whatever tool you use for your db-development. I would guess that your query does not work and therefore the creation of the recordgroup fails.
    the last thing andreas , i really appreciate your help , but you've posted about three posts in this thread without a vain ,
    and you could easily answer my simple question directly to accomplish my prompt task .
    Well, in my eyes this forum is here to give you assistance to solve your problems on your own, so that you are able so solve them alone next time. Just giving you the solution does not really help you as long as you don't understand them. You got a link to a blob-entry with a fully functionional example-tree, you have a working example on your own.
    the examples in the onlinehelp also uses lines and built-ins like i said before "connect prior and start with" , which i do not understand .
    But whats the implication of this? If you don't understand a specific detail, ask for that specific detail, but simple saying "It does not work, i don't understand why, give me a solution" is not the way it works (my personal opinion).
  • 7. Re: creating a simple  tree based on a query
    newbi_egy Explorer
    Currently Being Moderated
    you are a great instructor andreas , but i hope you have time to demonstrate what i do not understand :

    -i went to this link ,http://appsstuff.blogspot.com/2009/10/hierarchical-trees-developement-using.html
    because it has a simple example , and in the link you provided me has like you said :
    I want to show some different techniques to populate a tree and a way to interact with "standard" data-blocks.
    and i do not know the standard techniques originally to know the different ones .

    first of all ,
    i've tried this query in sql : "select 0 , level , dname , null , deptno from dept" , and it ends to this error :
    CONNECT BY clause required in this query block
    - and sure i could not do anything with this error , because i do not know what connect by clause is used for , or even
    what it means .? , however this "connect by" clause is mentioned in the example of the link i went to , which is above mentioned , but the instructor does not say anything about it , so i do not know what is it .
    - and there is a "start with" clause , i have seen before in the online help example , which i also do not understand what it means .?

    hope you will help me .

    thanks
  • 8. Re: creating a simple  tree based on a query
    Andreas Weiden Guru
    Currently Being Moderated
    select 0 , level , dname , null , deptno from dept
    LEVEL is a psudo-column which only exists in CONNECT BY-queries. As you do not have a CONNECT BY in your query, you cannot reference that column.You have to give a fixed value for that column and as you only have one level in your tree, simply use 1 instead of LEVEL.
  • 9. Re: creating a simple  tree based on a query
    newbi_egy Explorer
    Currently Being Moderated
    LEVEL à This is a specific pseudo-column Derived from “CONNECT BY”.
    if you can demonstrate this line , how is it derived from "connect by" , and when you told me to replace with "1" , is this "1" means something has something to do with the "level" pseudocolumn , or it is just any column to fill the vacuum ,
    like "select 1, 2, 3from dual " ?
    ICON à That contains the icon name of the Node (can be NULL).
    i've seen an example uses ''open'' for the "icon" column, and tried to use it but vainly , there is no icon appears , it is like i write "null" .

    - and you did not mention anything about the "conncet by" , or "start with" clause .
    as you only have one level in your tree .
    what is this mean , how 2 levels would look like ?

    thanks
  • 10. Re: creating a simple  tree based on a query
    Andreas Weiden Guru
    Currently Being Moderated
    if you can demonstrate this line , how is it derived from "connect by"
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/queries003.htm shows everything about hierarchical queries.
    and when you told me to replace with "1" , is this "1" means something has something to do with the "level" pseuocolumn , or it is just any column to fill the vacuum
    This is taken from the blob-entry i gave you in my first answer:
    The query has to be defined so that it has the following result-columns
    column-name      meaning
    NODE_STATE      defines, how the node is initially shown in the tree (-1 = collapsed,  0 = leaf-node,  1 = expanded)
    NODE_DEPTH      The tree-level the row it at. This column defines the hierarchy of the tree-data
    NODE_LABEL      The text to be shown in the tree-node
    NODE_ICON      The name of an icon-file which should be rendered for the treenode. The icon must be accessible to forms as any icon used, e.g. on a button
    NODE_VALUE      The "value" to be stored for the tree-node
    It states that the second columns NODE_DEPTH is the level of the node inside the tree and as you say you only have one level and no hierarchy in your tree, you simply do a
    SELECT ..., 1 NODE_DEPTH, ... FROM TABLE for this column
    i've seen an example uses ''open'' for the "icon" column, and tried to use it but vainly , there is no icon appears , it is like i write "null" .
    See the above column-description. The name has to be the name of an icon you can access in your form.If ou can use the icon on a button, you can also use it in a tree.
    - and you did not mention anything about the "conncet by" , or "start with" clause .
    See the first link.
    what is this mean , how 2 levels would look like ?
    If you have to levels, then you have a hierarchy which is expressed by giving different values for the NODE_DEPTH.

    Edited by: Andreas Weiden on 16.12.2012 14:14

Legend

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