This discussion is archived
13 Replies Latest reply: Jan 16, 2013 12:34 PM by newbi_egy RSS

tree with for-loops

newbi_egy Explorer
Currently Being Moderated
db and dev 10g rel2,
hi all ,
i am trying to create a tree with for_loops technique ,to show dnames as roots ,then "enames" under each department . but i am facing an error :
declare
        cursor dept_cur is
        select deptno , dname
        from   dept;
        
        cursor emp_cur (N number) is
        Select empno , ename
        from   emp
        where  deptno=N;

        Htree    item;

  parent_node varchar2(200);
  child_node  varchar2(200);
begin
        Htree:=Find_item('tree');
        For dept_R in Dept_cur loop
parent_node:=Ftree.Add_Tree_Node(htree,
Ftree.ROOT_NODE,
Ftree.PARENT_OFFSET,
Ftree.LAST_CHILD,
Ftree.EXPANDED_NODE,
dept_r.deptno||' '||dept_r.dname,
'insrec',
dept_r.deptno);

        Htree:=Find_item('tree');
        For emp_R in emp_cur(dept_r.deptno) loop
child_node:=Ftree.Add_Tree_Node(htree,
parent_node,                                                                -- the error reside here 
Ftree.PARENT_OFFSET,
Ftree.LAST_CHILD,
Ftree.EXPANDED_NODE,
emp_r.empno||' '||emp_r.ename,
'insrec',
emp_r.empno);
end loop;
end loop;
end;

the error tells me that : "identifier parent_node must be declared" , and the cursor highlights next to "parent_node" ?

i do not know how , as i tried another code with the same manner , and it works well , there are no errors .

tell me please what is the reason for that ? 

thanks                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 1. Re: tree with for-loops
    newbi_egy Explorer
    Currently Being Moderated
    i am so sorry , i've seen this example in a website , but applied it in a wrong way ,
    i thought that "parent_node" is a static value like "ftree.root_node" , but i figured out that it is a variable holds the node of the ftree._add_tree_node . i modified my code to this :
     
    declare 
         cursor dept_cur is select deptno , dname from dept ; 
         cursor emp_cur(n number) is select empno, ename from emp where deptno = n ; 
         parent_node ftree.node;
      child_node  ftree.node;
    
    begin 
         for dept_rec in dept_cur loop                                                  --first loop 
              parent_node := ftree.add_tree_node('b.tree4',
              ftree.root_node,
              ftree.parent_offset,
              Ftree.LAST_CHILD,
              Ftree.EXPANDED_NODE,
              dept_rec.dname,
              null, 
              dept_rec.deptno); 
              
              
    
              for emp_rec in emp_cur(dept_rec.deptno) loop                 -- second loop 
                   child_node := ftree.add_tree_node('b.tree4',
              parent_node,
              ftree.parent_offset,
              Ftree.LAST_CHILD,
              Ftree.EXPANDED_NODE,
              emp_rec.ename,
              null, 
              emp_rec.empno);  
              end loop ; 
         end loop ; 
         end ;
                   
    and it worked , but there is something i do not understand here :
    the second loop says : "select ename from emp where deptno = dept_rec.deptno(10) - at the first iteration
    then "select ename from emp where deptno = (20) or another 10 according to the value of dept_rec.deptno --at the second iteration .
    and so on .
    then what makes each employee is assigned to his department ? the code does not say so , the code 
    says to the engine "add the ename of the emp table where deptno = 10 as a node under the root node .
    
    i am sorry if i can not clear it well . 
    help if you get me . 
    thanks                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 2. Re: tree with for-loops
    Andreas Weiden Guru
    Currently Being Moderated
    the code says to the engine "add the ename of the emp table where deptno = 10 as a node under the root node
    No, it does not.

    In the second call to Ftree.Add_Tree_node you give the previously created node "parent_node" as parent.
  • 3. Re: tree with for-loops
    newbi_egy Explorer
    Currently Being Moderated
    but when i write this : regardless the rest of parameters (just "node FTREE.NODE, label VARCHAR2" )
    ftree.root_node,
    dept.dname -- then i am telling the engine to put root_nodes and make it's label as the names of dnames .

    that means , when i write this :

    parent_node -- ass a variable holds "accounting" for example
    emp.ename -- names of the employees where deptno = 10 then 20 then 30 then .........

    then i am telling the engine to make the "accounting" dname label to the ename where deptno = 10 ,20 ...
    that means changing "accounting" to 'smith' or 'clark' or 'scott' , then the same will happens with the next
    dname ?

    i know that i do not get it well , but i also think the code says so ?
    what is the wrong thing i can not get here ?

    thanks
  • 4. Re: tree with for-loops
    Andreas Weiden Guru
    Currently Being Moderated
    You should really start reading the online-help. When you give Ftree.ROOT_NODE as second parameter you define the parent-node of the currently created node is the tree's root. When you give an already cretaed department-node as second parameter, you define that the parent-node of the currently created node is that node.
  • 5. Re: tree with for-loops
    newbi_egy Explorer
    Currently Being Moderated
    You should really start reading the online-help. When you give Ftree.ROOT_NODE as second parameter you >define the parent-node of the currently created node is the tree's root. When you give an already cretaed >department-node as second parameter, you define that the parent-node of the currently created node is that >node.
    i've searched the online help again for add_tree_node built in , and there is nothing demonstrates what you are saying .

    and searched for "ftree.root_node" and there is no thread about it on the online help >

    if you could give me an example demonstrates your words please ?

    thanks
  • 6. Re: tree with for-loops
    ck Explorer
    Currently Being Moderated
    hello,

    try to look at this if im getting your point.

    http://andreas.weiden.orcl.over-blog.de/article-29307730.html

    written by mr. andres himself.
  • 7. Re: tree with for-loops
    newbi_egy Explorer
    Currently Being Moderated
    When you give Ftree.ROOT_NODE as second parameter you >define the parent-node of the currently created >node is the tree's root. When you give an already cretaed >department-node as second parameter, you define >that the parent-node of the currently created node is that >node.
    i mean just an example for clarifying only this point .
    thanks
  • 8. Re: tree with for-loops
    Andreas Weiden Guru
    Currently Being Moderated
    I don't know how to be any more clear:

    The parameter parent_node specifies the tree-node under which the newly created node will be placed.

    And... you have a working example.
  • 9. Re: tree with for-loops
    newbi_egy Explorer
    Currently Being Moderated
    1- what i do not get is that the first cursor in the first loop retrieves
    ACCOUNTING 10
    RESEARCH 20
    SALES 30
    OPERATIONS 40
    2- the second cursor in the second loop retrieves enames where deptno = dept_rec.deptno(10) .
    so the emp_rec.ename holds
    CLARK 10
    KING 10
    MILLER 10
    3- the parent_node variable holds "accounting" at the first iteration ,
    -what happens is when the parent_node is accounting at the first iteration , the labels
    CLARK
    KING
    MILLER are assigned as nodes , and this what i do not get , the emp_rec.ename holds three values .
    in order to be posted as nodes , the loop has to iterate three times , and this does not happen because when it iterates for the second time , the "ACCOUNTING" value should change to the next dname which is "RESEARCH" , so the RESEARCH node should hold "KING", then
    "SALES" node should hold "MILLER" .
    but that does not happen , what happens is there are three values assigned to the first node"ACCOUNTING" at the first iteration ?
    how is that could be ?

    i am asking to learn , and i think i've something to ask .

    a lot of thanks to you .
  • 10. Re: tree with for-loops
    Andreas Weiden Guru
    Currently Being Moderated
    for dept_rec in dept_cur loop --first loop
      parent_node := ftree.add_tree_node('b.tree4',ftree.root_node,ftree.parent_offset,Ftree.LAST_CHILD,Ftree.EXPANDED_NODE,dept_rec.dname,null,dept_rec.deptno);
    
      for emp_rec in emp_cur(dept_rec.deptno) loop -- second loop
        child_node := ftree.add_tree_node('b.tree4',parent_node,ftree.parent_offset,Ftree.LAST_CHILD,Ftree.EXPANDED_NODE,emp_rec.ename,null,emp_rec.empno);
      end loop ;
    end loop ;
    Whats's unclear here?
    The first loop iterates over the departments. For each department it create a new node. Then it iterates over all the employees for that specific department and create nodes with it.
  • 11. Re: tree with for-loops
    newbi_egy Explorer
    Currently Being Moderated
    that what i said above andreas , it posts three values for the first department"ACCOUNTING" at the first iteratation. how ?
    the loop has to iterate three times to assign these three values , and if if iterates , sure the department name has to be changed .

    i think my point is so clear in the previous post .
    generally , thanks a lot
  • 12. Re: tree with for-loops
    Andreas Weiden Guru
    Currently Being Moderated
    it posts three values for the first department"ACCOUNTING" at the first iteratation. how ?
    Maybe by iterating three times in the inner loop?
    and if if iterates , sure the department name has to be changed .
    Why, you have two loops, one outer loop for the departments and one inner loop for the employees per departments
    i think my point is so clear in the previous post .
    Well, not for me. I still don't understand whats your problem. Is the output not as expected? If so, you might post the expected output and the one you get.
  • 13. Re: tree with for-loops
    newbi_egy Explorer
    Currently Being Moderated
    Is the output not as expected?
    no , it is as expected . but i am asking to understand not to just do the task .
    if you remeber , i think you've said something like that :
    as per my point , this thread is to help you think and figure out the problem yourself .
    something like that

Legend

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