Thread: Script to CTE

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    5
    Rep Power
    0

    Script to CTE


    Hi,

    I have a table which has 2 columns and many rows (below is a example : original table has around ~2k lines in it ).

    2 columns are

    user manager

    EG :

    john peter
    joe peter
    sam peter
    jill boyd
    rav boyd
    venkat raj
    surku raj
    domc joe
    hans joe
    jeen rav
    pam surku
    harry domc

    I need to have a script which can give me output which will have directreport till 2 level deep ( Ignore anything which is more than 2 levels deep )

    manager, directreport, directreport1, directreport2, directmanager1, directmanager2

    so for abovve the output should be

    peter john null null null null
    peter joe null null null null
    peter sam null null null null
    peter null domc null joe null
    peter null hans null joe null
    peter null null harry null domc
    boyd jill null null null null
    boyd rav null null null null
    boyd null jeen null rav null
    raj venkat null null null null
    raj surku null null null null
    joe domc null null null null
    joe hans null null null null
    joe null harry null domc null
    rav jean null null null null
    surku pam null null null null
    domc harry null null null null
  2. #2
  3. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,254
    Rep Power
    1810
    In what way are 'Common Table Expressions' involved?

    Does this data come from a database? If so, is there more data available than that which you provided? Because those names are not a good way to determine uniqueness. It is likely that two instances of John will not refer to the same person.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    5
    Rep Power
    0
    yes the data is coming from a database.

    Regarding the names actual table uses a unique identity and there will not be duplication of names.
  6. #4
  7. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,254
    Rep Power
    1810
    Sorry for the late response.

    Depending on your database and table, you may find the task much easier using a select statement than trying to do the job externally in perl. Example here at wikipedia:

    Hierarchical and recursive queries in SQL

    As you can see from the first example, selecting the level makes formatting the output much easier.

    But I believe the task could be accomplished in perl alone if uniqueness of entries was guaranteed (say by selecting the row_id rather than just the name. It would probably involve looping over the data more than once to locate the level in the hierarchy however.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    5
    Rep Power
    0
    Thank you for your reply.

    I will go through the link. Seems to be complicated using sql query as well.

    I am new to scripting. If you have any sample code would be great.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2012
    Posts
    830
    Rep Power
    496
    If you want to do it in pure Perl, rather than SQL, then using two hashes should get you there.

    One simple hash to go up the hierarchy: key is the report and value the manager (assuming any report has only one direct manager)

    One hash of arrays to go down the hierarchy; key is the manager and the array contains all direct reports.

    Populate the two hashes when you read the input.

    Then you just have to go through the hashes to prepare your output.

IMN logo majestic logo threadwatch logo seochat tools logo