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. Donnie28

    Donnie28 Active Member

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

    My name is Donal and I'm new to the site so just wanted to introduce myself and say hi and post my first question so let me know if I am making any stupid mistakes as I may be a bit green on how this works!

    Basically I have data stored in a 2 fields as follows:

    Org_ Parent
    Org_child

    The Org_Parent field is repeated with the same value 3 times but the org_child has 3 separate values. I need a query that will represent the data in a flat format .i.e in 4 separate fields as follows:

    cost centre(org_parent field):
    Sub-Division(first value in org_child field): will always start with a 6 digit number
    Division(second value in org_child field): will always start with a 4 digit number ending with 2 zeros
    Branch(third value in org_child field): will always start with a 4 digit number ending with 3 zeros

    See attached excel spread sheet for how data currently looks and how it should look after the query

    Any help on this would be very much appreciated

    Regards
    Donal
     

    Attached Files:

  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If you're running 11.2 or later you can do this:


    Code (SQL):

    SQL> SELECT org_parent, listagg(org_child, ' ') WITHIN GROUP (ORDER BY org_parent) child_org
      2  FROM org
      3  GROUP BY org_parent;


    ORG_PARENT CHILD_ORG
    ---------- --------------------------------------------------------------------------------
       9970352 4000(I&P) 4100(Branch) 410100(Sales & Marketing)


    SQL>
     
     
    Donnie28 likes this.
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,Donnie28.
    you have not a full hierarchical structure.

    Present Hierarchial Format
    Org_child Org_parent
    410100(sales & marketing) 9970352
    4100(Branch) 9970352
    4000(I &P) 9970352

    if you add structure, then you will be able to receive the result of hierarchical sql-query which look as described in your xls the file.


    See links:

    http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm
    http://www.orafaq.com/node/2038



    Otherwise you will be able to receive result, only as "string concatenation". An example to you was set already by David.
     
    Donnie28 likes this.
  4. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Thanks for the reply David its much appreciated I will try this out today and let you know how I get on.
     
  5. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Thanks for the reply Sergey its much appreciated, I will have a llok at these today
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    For example :
    Code (SQL):


    WITH
    org AS
    (
    SELECT '410100(sales  marketing)' Org_child , 9970352 Org_parent FROM dual UNION ALL
    SELECT '4100(Branch)', 9970352 FROM dual UNION ALL
    SELECT '4000(I P)', 9970352  FROM dual
    )
    ,
    results AS
    (
     SELECT r.*,
            ROW_NUMBER() OVER (partition BY r.org_parent ORDER BY to_number(regexp_substr(r.org_child,'\d+',1,1))) rn
       FROM org r
    )
    SELECT
           rs.org_parent
           ,MAX(CASE WHEN rn = 3 THEN org_child END )  SubDivision
           ,MAX(CASE WHEN rn = 2 THEN org_child END )  Division
           ,MAX(CASE WHEN rn = 1 THEN org_child END )  Branch
    FROM results rs
    GROUP BY
          rs.org_parent;

    SQL>
     
    ORG_PARENT SUBDIVISION              DIVISION                 BRANCH
    ---------- ------------------------ ------------------------ ------------------------
       9970352 410100(sales  marketing) 4100(Branch)             4000(I P)

     



    Code (SQL):



     WITH
    org AS
    (
    SELECT 410100 org_child,'410100(salesmarketing)' Org_child_name , 4100 Org_parent FROM dual UNION ALL
    SELECT 4100,'4100(Branch)', 4000 FROM dual UNION ALL
    SELECT 4000,'4000(IP)', NULL  FROM dual
    )
    SELECT
           r.org_parent,
           connect_by_root org_child_name  branch,
           prior           org_child_name  division,          
           org_child_name                  subdivision          
    FROM org r
    WHERE level = 3
    START WITH org_parent IS NULL
    CONNECT BY prior org_child = org_parent;

    SQL>
     
    ORG_PARENT BRANCH                 DIVISION               SUBDIVISION
    ---------- ---------------------- ---------------------- ----------------------
          4100 4000(IP)               4100(Branch)           410100(salesmarketing)
     

     
     
  7. Donnie28

    Donnie28 Active Member

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

    Have tried the but just gives me back a cannot return a resultset from the query error but I am accessing the DB through a VJDBC connection in Plateau Report Developer and I think the DB is only 10g

    Donal
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    For 10g it is possible to use :

    Code (SQL):

    WITH
    org AS
    (
    SELECT '410100(salesmarketing)' Org_child,9970352 Org_parent FROM dual UNION ALL
    SELECT '4100(Branch)', 9970352 FROM dual UNION ALL
    SELECT '4000(IP)', 9970352  FROM dual
    )
    SELECT
           r.org_parent,
           CAST(XMLAGG (XMLELEMENT(org_child,r.org_child||' ') ORDER BY to_number(regexp_substr(r.org_child,'\d+',1,1)) DESC).EXTRACT ('//text()') AS varchar2(255)) str_agg      
    FROM  org r      
    GROUP BY  
    r.org_parent;

    SQL>
     
    ORG_PARENT STR_AGG
    ---------- --------------------------------------------------------------------------------
       9970352 410100(salesmarketing) 4100(Branch) 4000(IP)
     

     
     
  9. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey, have tried both oof these queries above but just get the same error when running them(see below). I using Plateau Report Developer and connecting through a VJDBC connection. I wonder if this might be causing he issue?

    Error when running the SQL queries

    Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the result set metadata.
    org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
    SQL error #1: java.sql.SQLException: java.sql.SQLException: ORA-00923: FROM keyword not found where expected
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    What the os , os version ?
    there are tools: sqlplus or sql developer?
     
  11. Donnie28

    Donnie28 Active Member

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

    its Windows 7 Enterprise. I think there is alot of restrictions around accessing the database so it may be hard to get acces to these tools

    Regards
    Donnie28
     
  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Tell, and in the tool "Plateau Report Developer" it is possible to see sql request which the environment is going to execute.... I think that he ("Plateau Report Developer") "breaks" a SQL query before running
     
  13. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80


    I can see the SQL requests and make some simple amendments but it can be painful trying to execute any queries that are remotely complex! not sure what you mean by PRD breaking SQL queries before running them? Will this cause a problem going forward as I was planning to develope some reports and add some complexity to some of the queries but if it is going to be very difficult in PRD I will have to look at other options. Any advice is greatly appreciated
     
  14. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Check your actual SQL statement and make sure there is a space between the 'FROM' declaration and whatever the query is before it. Make sure you also do not have a comma before it.

    Note : All sql-queries which were given here - all work if to launch in: sqlpus, sql developer, pl/sql developer, toad....
     
  15. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Okay Sergey will have a mess around with the syntax and see how I get on, thanks again for your help
     
  16. Donnie28

    Donnie28 Active Member

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

    Just looking back on this thread I realised that some of the info I provided was misleading in that the values for each field are not static. See attachement

    this is just a sample of the data and there are other values for each of the ORG_ID, COST_CENTRE,DEPARTMENT & MANAGE UNIT fields.

    Thanks and hope this makes things easier to understand

    Regards
    Donal
     

    Attached Files:

  17. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,Donal.
    Generally carries then request looks so ( level is known and also sorting order):

    Code (SQL):


    WITH
    org AS (    
    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
    )
    ,preres AS
    (
    SELECT  
          r.*,
          ROW_NUMBER() OVER (partition BY org_id ORDER BY nlssort(org_id_parent,'NLS_SORT=BINARY')) rn
    FROM org r
    )

    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 preres pr
    GROUP BY
         pr.org_id
    ORDER BY org_id DESC;

    SQL >
    SQL>
     
        ORG_ID COST_CENTRE DEPARTMENT MANAGE_UNIT
    ---------- ----------- ---------- -----------
        990733 2           2DS        ILP
        610150 3           3CR        ILG
        312156 5           5YJ        IJK
        213459 4           4GH        IKL
     



     
     
  18. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Thanks for the reply....unfortunately still getting a syntax error but PRD is not intuitive enough to tell me where so bit like looking for a needle in haystack!!!
     
  19. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    try this :
    Code (SQL):

    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;
     
     
  20. Donnie28

    Donnie28 Active Member

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

    Uufortuately still getting a syntax error when running the above. I have attached the error

    Regards
    Donal
     

    Attached Files: