1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query to output Hierarchical structure in flat format

Discussion in 'SQL PL/SQL' started by Donnie28, Apr 10, 2014.

  1. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.

    Check simple request: select banner from v$version
     
  2. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    BANNER
    Hi Sergey

    Heres the info I got back from the request:

    TNS for Linux: Version 11.2.0.2.0 - Production
    PL/SQL Release 11.2.0.2.0 - Production
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    NLSRTL Version 11.2.0.2.0 - Production
    CORE 11.2.0.2.0 Production
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Its - ok.
    Try step two :

    Code (SQL):

    SELECT
        z.TABLE_NAME,z.TABLESPACE_NAME,COUNT(tc.COLUMN_ID) cnt_cols
    FROM    
    (SELECT ut.TABLE_NAME,ut.TABLESPACE_NAME  FROM user_tables ut WHERE rownum < 2) z
    JOIN user_tab_cols tc ON tc.TABLE_NAME = z.TABLE_NAME
    GROUP BY z.TABLE_NAME,z.TABLESPACE_NAME;
     
     
  4. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey

    ran the query and just ge a syntax error. see attached

    Regards
    Donal
     

    Attached Files:

  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    The problem with settings of your tool means at you.....

    Questions for specification :
    1) select you can give a section of a java-code in which there is a SQL expression assembly ?

    2) version jdbc ?
    version jre ?

    3)
    test java code :test.java

    Code (SQL):

        import java.SQL.*;
        import oracle.jdbc.driver.*;

        public class test
        {
             public static void main(String[] args)
             {
                  String url =  url = "jdbc:oracle:thin:@YOUR_HOST:1521:YOUR_SID";


              try {
                  DriverManager.registerDriver (
                  NEW oracle.jdbc.driver.OracleDriver());
                  }
              catch (Exception e) { RETURN; }

              try {
                 Connection cn =  DriverManager.getConnection (url,"your_login","yor_passwd");


                 Statement st = cn.createStatement();
                 ResultSet rs =
                        st.executeQuery ("SELECT"+
                                            "z.TABLE_NAME,z.TABLESPACE_NAME,COUNT(tc.COLUMN_ID) cnt_cols "+
                                            "FROM     "+
                                            "(SELECT ut.TABLE_NAME,ut.TABLESPACE_NAME  FROM user_tables ut WHERE rownum < 2) z "+
                                            "JOIN user_tab_cols tc ON tc.TABLE_NAME = z.TABLE_NAME "+
                                            "GROUP BY z.TABLE_NAME,z.TABLESPACE_NAME"
                                        );

                while (rs.NEXT()) {
                System.OUT.println("TABLE_NAME=" + rs.getString(1) + " " +
                                     "TABLESPACE_NAME=" + rs.getString(2))+ " " +
    "CNT_COLS = " + rs.getString(3))
    ;
                                  }

                 st.close();
                 cn.close();
               }
             catch (Exception e) { RETURN; }
             finally { System.OUT.println("All that happened"); }
             }
        }

     
     
  6. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey

    Sorry didn't fully understand your last message. Are you saying there is a problem with the settings of the tool? The Plateau Report Designer tool connects to the Oracle Database through a VJDBC connection. The connection string is as follows:

    jdbc:vjdbc:servlet:https://permanent-stage.plateau.com/vjdbc/vjdbc,db10g

    There is also a Username and password that is required upon connnection which is input manually. I'm not exactly sure what I am suppose to do with the code you provided, I tried running it in the SQL editor in Plateau Report Designer but still doesn't run...surprise surprise! I have provided a screenshot of the connection box I get when connectig to the database

    Regards
    Donal
     

    Attached Files:

  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Sorry ,but link not open... error:
    'Forbidden

    You don't have permission to access /vjdbc/vjdbc,db10g on this server.
    Additionally, a 500 Internal Server Error error was encountered while trying to use an ErrorDocument to handle the request.
    Apache/2.2.22 (Linux/SUSE) Server at permanent-stage.plateau.com Port 80'



    I brought for an example of simple java-code for autonomous dough, i.e. run in JVM.
    I believe that in your environment when you enter sql-query into SQL_Editor, there are problems with multilines queries (difficult) . most likely queries have to be issued that there were no syntactic mistakes...

    earlier you used queries of difficult structure in this tool?
    or you wrap difficult queries in table(and pipelined) functions, or you use cursors, etc.?

    For test query :

    1) select 1 from dual
    union all
    select 2 from dual

    2) select * from (select level from dual connect by level <8)

    Note: I saw in the my practice of report-systems which didn't support difficult sql - queries...
     
  8. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    HI Sergey

    After quite a few hours of going through the code you posted earlier below and looking at the error returned I finally figured out what the issue was..... the semi-colon is classed as an invalid character in the SQL editor of Plateau Report Designer so when I removed it from the last line... ORDER BY org_id DESC.... the query ran and returned the 4 hard-coded rows below!! yipeeee :)....now what I want to do is for the query to return all data from the database( which is all in the same format of the hard coded data) and not just the hard coded values as there are many other values in the ORG_ID & ORG_ID_PARENT fields. Is this possible? Thanks you so much for sticking with me on this it is very much appreciated

    Regards
    Donal

    SELECT
    pr.org_id,
    MAX(CASE WHEN rn = 1 THEN pr.ORG_ID_PARENT END ) COST_CENTRE,
    MAX(CASE WHEN rn = 2 THEN pr.ORG_ID_PARENT END ) DEPARTMENT,
    MAX(CASE WHEN rn = 3 THEN pr.ORG_ID_PARENT END ) MANAGE_UNIT
    FROM
    (
    SELECT
    r.*,
    ROW_NUMBER() OVER (partition BY org_id ORDER BY nlssort(org_id_parent,'NLS_SORT=BINARY')) rn
    FROM (
    SELECT 990733 ORG_ID ,'2' ORG_ID_PARENT FROM dual UNION ALL
    SELECT 990733 ,'2DS' FROM dual UNION ALL
    SELECT 990733 ,'ILP' FROM dual UNION ALL
    SELECT 610150 ,'3' FROM dual UNION ALL
    SELECT 610150 ,'3CR' FROM dual UNION ALL
    SELECT 610150 ,'ILG' FROM dual UNION ALL
    SELECT 213459 ,'4' FROM dual UNION ALL
    SELECT 213459 ,'4GH' FROM dual UNION ALL
    SELECT 213459 ,'IKL' FROM dual UNION ALL
    SELECT 312156 ,'5' FROM dual UNION ALL
    SELECT 312156 ,'5YJ' FROM dual UNION ALL
    SELECT 312156 ,'IJK' FROM dual
    )r
    ) pr

    GROUP BY
    pr.org_id
    ORDER BY org_id DESC
     
  9. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi,Donnie.

    what type of result is necessary, i.e. new columns will be added?

    If the type of result is necessary another, then give an example of basic data and a type of result.

    Then it will be possible to adapt request.

    note : You will need to set a maximum level of possible hierarchy always.
     
  10. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey


    I have attached the full dataset of what needs to be returned, and the 4 fields that are required to be displayed. The ORG_ID field in the database stores the Cost Centre field and the ORG_ID_PARENT stores the other 3 fields. There will be repeating fields in the Department field( 5 unique values ), Division Field( 15 unique values ), Management Unit( 61 unique values) and in the Cost Centre field ther are 166 unique values so I would expect ther result to return 166 rows. hope this makes more sense to find a solution, please let me know if you need clarification on anything

    Donal
     

    Attached Files:

  11. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    With result-set everything is clear.

    Some specification....
    Source data how are stored?
    you can provide example ?
    ORG_ID_PARENT is Department||Deivision||ManagmentUnit ?
     
  12. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey

    The sources data is stored in an Oracle 11.2g database in the LMS system. See attached how the data is stored in the ORG_TABLE

    Regards
    Donal
     

    Attached Files:

  13. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey

    just to clarify sheet 3 shows the current structure of the ORG_TABLE and sheet 1 shows how it will look after the query is run

    Regards
    Donal
     
  14. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi,Donal.

    Here one of options :

    Code (SQL):


    WITH
    preres AS
    (
    SELECT
        r.org_id
        ,r.parent_id
        ,ROW_NUMBER() OVER (partition BY r.org_id ORDER BY parent_id) rn
    FROM org_table r
    )

    SELECT
        MAX(CASE WHEN rn = 1 THEN pr.parent_id END ) "Department",
        MAX(CASE WHEN rn = 2 THEN pr.parent_id END ) "Division",
        MAX(CASE WHEN rn = 3 THEN pr.parent_id END ) "Management Unit" ,
        pr.org_id                                    "Cost Centre"    
    FROM preres pr
    GROUP BY pr.org_id

     
     
    Donnie28 likes this.
  15. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey

    Thanks for the reply....its just gives me back a ORA-00900: invalid SQL statement error but can't see where the error is! See attached

    Regards
     

    Attached Files:

  16. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Then use other syntax

    Code (SQL):

    SELECT
        MAX(CASE WHEN rn = 1 THEN pr.parent_id END ) "Department",
        MAX(CASE WHEN rn = 2 THEN pr.parent_id END ) "Division",
        MAX(CASE WHEN rn = 3 THEN pr.parent_id END ) "Management Unit" ,
        pr.org_id                                    "Cost Centre"    
    FROM (
    SELECT
        r.org_id
        ,r.parent_id
        ,ROW_NUMBER() OVER (partition BY r.org_id ORDER BY parent_id) rn
    FROM org_table r
    )  pr
    GROUP BY pr.org_id
     
    pay attention that problems can cause of the name of columns framed in"
     
    Donnie28 likes this.
  17. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Yesss that has worked :)...thanks you so much for all your help Sergey and sticking with this...its much apprecaited :)
     
  18. dendroidking

    dendroidking Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    ho chi minh
    Nice topic, i can learn more things :)
     
  19. takeda14121990

    takeda14121990 Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    canada
    Yes, this is great ideal, and should try it