13 Replies Latest reply: Jan 16, 2013 2:34 PM by salute-Salem RSS

    tree with for-loops

      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 :
              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);
              For dept_R in Dept_cur loop
      dept_r.deptno||' '||dept_r.dname,
              For emp_R in emp_cur(dept_r.deptno) loop
      parent_node,                                                                -- the error reside here 
      emp_r.empno||' '||emp_r.ename,
      end loop;
      end loop;
      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 ? 
        • 1. Re: tree with for-loops
          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 :
               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;
               for dept_rec in dept_cur loop                                                  --first loop 
                    parent_node := ftree.add_tree_node('b.tree4',
                    for emp_rec in emp_cur(dept_rec.deptno) loop                 -- second loop 
                         child_node := ftree.add_tree_node('b.tree4',
                    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 . 
          • 2. Re: tree with for-loops
            Andreas Weiden
            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
              but when i write this : regardless the rest of parameters (just "node FTREE.NODE, label VARCHAR2" )
              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 ?

              • 4. Re: tree with for-loops
                Andreas Weiden
                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
                  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 ?

                  • 6. Re: tree with for-loops

                    try to look at this if im getting your point.


                    written by mr. andres himself.
                    • 7. Re: tree with for-loops
                      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 .
                      • 8. Re: tree with for-loops
                        Andreas Weiden
                        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
                          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
                          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
                            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
                              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
                                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
                                  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