This discussion is archived
6 Replies Latest reply: Feb 19, 2013 10:13 PM by 979801 RSS

hierarchy query Problem

979801 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

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