6 Replies Latest reply: Feb 20, 2013 12:13 AM by 979801 RSS

    hierarchy query Problem

    979801
      hello,
      I want to know that, I have a table(clumns--> id,class_id,section_id,strength) named as section_allocation table,
      An another table that has columns--> roll_no,student_name,class_id,section_id,
      how to make a hierarchy query to show all classes under this all related sections and under this all related students.
      Actually I have to make an oracle tree in oracle form to show hierarchy as:

      |__Class I
      |____Section A
      |______ A st -- Student _name
      |______ B st -- Student _name
      |____Section B
      |______ A2 -- Student _name
      |__Class II
      |____Section A

      Edited by: 976798 on Feb 16, 2013 1:12 AM
        • 1. Re: hierarchy query Problem
          Frank Kulash
          Hi,

          Here's one way:
          SELECT       CASE
                     WHEN  GROUPING (s.section_id)   = 1
                     THEN  s.class_id
                     WHEN  GROUPING (a.student_name) = 1
                     THEN  '    ' || s.section_id
                     ELSE  '        ' || a.student_name
                 END          AS all_names
          FROM              section_allocation  s
          LEFT OUTER JOIN      another_table          a  ON   a.section_id  = s.section_id
                                               AND  a.class_id        = s.class_id
          GROUP BY  s.class_id
          ,            ROLLUP (s.section_id, a.student_name)
          ORDER BY  s.class_id
          ,            s.section_id          NULLS FIRST
          ,       a.student_name     NULLS FIRST
          ;
          The outer join means that all classes and sections will be displayed, even if no students are enrolled in them.
          If you want only the sectilons that have students enrolled, then you don't need a join at all; you can use the class_id and section_id from the second table.

           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: hierarchy query Problem
            979801
            Thanx for reply,
            I am using oracle 11g sql developer tool and oracle 11g forms. Actually I want to write a simple hierarchy query to populate all student name(from student_detail table).

            My tables structure are  as
            CLASS_MSTR (CLASS_ID     NUMBER(38,0),CLASS_NM     VARCHAR2(20 BYTE))
            SECTION_MSTR (SEC_ID     NUMBER(38,0),SEC_NM     VARCHAR2(4 BYTE))
            TBL_STD_DTL (STD_ID     NUMBER(38,0),STD_NAME     VARCHAR2(50 BYTE),FATHER_NM     VARCHAR2(50 BYTE),CLASS_ID     NUMBER(38,0), SEC_ID     NUMBER(38,0),
            ADDRESS VARCHAR2(100 BYTE), PRV_CLASS_ID     NUMBER(38,0))
            TBL_SEC_ALOT (LOC_ID     NUMBER, CLASS_ID     NUMBER, SECTION_ID     NUMBER(38,0), STRNG     NUMBER(38,0))

            Respectively dummy date are
            Class Master Table Date
            Insert into CLASS_MSTR (CLASS_ID,CLASS_NM)values (1,'8th');
            Insert into CLASS_MSTR (CLASS_ID,CLASS_NM) values (2,'9th');
            Insert into CLASS_MSTR (CLASS_ID,CLASS_NM) values (3'10th');

            Section Master Table Data
            Insert into SECTION_MSTR (SEC_ID,SEC_NM) values (1,'A');
            Insert into SECTION_MSTR (SEC_ID,SEC_NM) values (2,'B');
            Insert into SECTION_MSTR (SEC_ID,SEC_NM) values (3,'C');
            Insert into SECTION_MSTR (SEC_ID,SEC_NM) values (4,'D');

            Section Allotement Table Data
            Insert into TBL_SEC_ALOT (LOC_ID,CLASS_ID,SECTION_ID,STRNG) values (1,1,1,20);
            Insert into TBL_SEC_ALOT (LOC_ID,CLASS_ID,SECTION_ID,STRNG) values (2,1,null,15);
            Insert into TBL_SEC_ALOT (LOC_ID,CLASS_ID,SECTION_ID,STRNG) values (4,2,2,10);
            Insert into TBL_SEC_ALOT (LOC_ID,CLASS_ID,SECTION_ID,STRNG) values (3,2,1,20);

            Student Detail Table Data
            Insert into TBL_STD_DTL (STD_ID,STD_NAME,FATHER_NM,CLASS_ID,SEC_ID,ADDRESS,PRV_CLASS_ID) values (1,'ANNU','AJAY',1,1,'Vijay Nagar',1);
            Insert into TBL_STD_DTL (STD_ID,STD_NAME,FATHER_NM,CLASS_ID,SEC_ID,ADDRESS,PRV_CLASS_ID) values (2,'SANTOSH','INDER JEET',2,1,'Ghaziabad',1);


            Now I want populate all data into an oracle tree for which I have to create a record group with a hierarchy query using connect by.
            data order in oracle tree
            __tree Name__
            *8th* =>Class Name
            A =>Under Class 8th all section defined by section allocation table
            ANNU =>Student Name from Student Detail Table.
            null
            *9th*
            A
            SANTOSH
            B
            *10th*
            A

            Edited by: 976798 on Feb 18, 2013 2:23 AM
            • 3. Re: hierarchy query Problem
              Frank Kulash
              Hi,
              976798 wrote:
              Thanx for reply,
              I am using oracle 11g sql developer tool and oracle 11g forms. Actually I want to write a simple hierarchy query to populate all student name(from student_detail table).
              Sorry, I don't understand.
              If you're using "populate" in the usual way, meansing "add new data to a table", which table(s) do you want to change? What will be the contents of that table when everything is finished?
              If you're using "populate" to mean something else, what is iot?
              My tables structure are  as
              CLASS_MSTR (CLASS_ID     NUMBER(38,0),CLASS_NM     VARCHAR2(20 BYTE))
              SECTION_MSTR (SEC_ID     NUMBER(38,0),SEC_NM     VARCHAR2(4 BYTE))
              TBL_STD_DTL (STD_ID     NUMBER(38,0),STD_NAME     VARCHAR2(50 BYTE),FATHER_NM     VARCHAR2(50 BYTE),CLASS_ID     NUMBER(38,0), SEC_ID     NUMBER(38,0),
              ADDRESS VARCHAR2(100 BYTE), PRV_CLASS_ID     NUMBER(38,0))
              TBL_SEC_ALOT (LOC_ID     NUMBER, CLASS_ID     NUMBER, SECTION_ID     NUMBER(38,0), STRNG     NUMBER(38,0))

              Respectively dummy date are
              Class Master Table Date
              Insert into CLASS_MSTR (CLASS_ID,CLASS_NM)values (1,'8th');
              Insert into CLASS_MSTR (CLASS_ID,CLASS_NM) values (2,'9th');
              Insert into CLASS_MSTR (CLASS_ID,CLASS_NM) values (3'10th');

              Section Master Table Data
              Insert into SECTION_MSTR (SEC_ID,SEC_NM) values (1,'A');
              Insert into SECTION_MSTR (SEC_ID,SEC_NM) values (2,'B');
              Insert into SECTION_MSTR (SEC_ID,SEC_NM) values (3,'C');
              Insert into SECTION_MSTR (SEC_ID,SEC_NM) values (4,'D');

              Section Allotement Table Data
              Insert into TBL_SEC_ALOT (LOC_ID,CLASS_ID,SECTION_ID,STRNG) values (1,1,1,20);
              Insert into TBL_SEC_ALOT (LOC_ID,CLASS_ID,SECTION_ID,STRNG) values (2,1,null,15);
              Insert into TBL_SEC_ALOT (LOC_ID,CLASS_ID,SECTION_ID,STRNG) values (4,2,2,10);
              Insert into TBL_SEC_ALOT (LOC_ID,CLASS_ID,SECTION_ID,STRNG) values (3,2,1,20);

              Student Detail Table Data
              Insert into TBL_STD_DTL (STD_ID,STD_NAME,FATHER_NM,CLASS_ID,SEC_ID,ADDRESS,PRV_CLASS_ID) values (1,'ANNU','AJAY',1,1,'Vijay Nagar',1);
              Insert into TBL_STD_DTL (STD_ID,STD_NAME,FATHER_NM,CLASS_ID,SEC_ID,ADDRESS,PRV_CLASS_ID) values (2,'SANTOSH','INDER JEET',2,1,'Ghaziabad',1);


              Now I want populate all data into an oracle tree for which I have to create a record group with a hierarchy query using connect by.
              Why do you need to use CONNECT BY? If some other technique, such as GROUP BY ... ROLLUP, does the job more simply and more efficiently, wouldn't you want that?
              data order in oracle tree
              Is the text below supposed to be indented? See the forum FAQ {message:id=9360002} for how to use \
               tags to post formatted text.               
              
              __tree Name__
              *8th* =>Class Name
              A =>Under Class 8th all section defined by section allocation table
              ANNU =>Student Name from Student Detail Table.
              null
              *9th*
              A
              SANTOSH
              B
              *10th*
              A
              Explain how you get these results.  For example, why is 'A' under '10th'?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
              • 4. Re: hierarchy query Problem
                979801
                By mistake I typed the dummy data wrong.There should be under 10th class, section is B instead of A.Please concentrate just on the methodology and output format not on the dummy data.If You assumes the correction in the dummy data should be then you may change it.
                Actual problem is that how to sort all students under a tree item in oracle form.
                Means
                At first level of tree ----All classes.
                2nd level of tree----Concerned Sections.
                3rd level of tree-----Concerned Students Name.
                So Please give me a solution Its an urgent.
                Thank You.
                • 5. Re: hierarchy query Problem
                  sb92075
                  976798 wrote:
                  So Please give me a solution Its an urgent.
                  Please explain & clarify why is it urgent for me to provide you a solution?

                  The lack of planning on your part does NOT constitute an emergency on my part.

                  Handle:     976798
                  Status Level:     Newbie
                  Registered:     Dec 13, 2012
                  Total Posts:     16
                  Total Questions:     5 (4 unresolved)

                  Why do you waste your time & our time here since you rarely get your questions answered?
                  • 6. Re: hierarchy query Problem
                    979801
                    Thank You Very much.
                    I think here we are to discuss not to argue.Any way My mean was that the solution is urgent for me not for you so please Interpret correctly. You are or anybody supposed to here to just help to answer If you Could not answer or not know how to do this then you should not reply any one's problem.