Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2016
    Posts
    2

    Unanswered: Pervasive SQL recursive (hierarchical) queries

    Hi,
    I'm new to Pervasive, so wondering how recursive queries can be accomplished with PSQL. I have used Oracle connect by prior, and e.g. SQL Server CTE queries, but Pervasive doesn't have similar available?
    I have big table having hierarchical data for "unknown" depths, and I would need to present full hierarchy for one parent. How could I accomplish that?

    This is just an example table
    +----+------------+-----------+------------+
    | id | first_name | last_name | manager_id |
    +----+------------+-----------+------------+
    | 1 | David | Whatever | 4 |
    | 2 | Henry | Whatever | 4 |
    | 3 | Harry | Whatever | 5 |
    | 4 | Peter | Whatever | NULL |
    | 5 | Heidi | Whatever | 4 |
    | 6 | Terry | Whatever | 1 |
    +----+------------+-----------+------------+

    Query below would only return one level from the parent:

    select e.first_name as employee, m.first_name as reports_to
    from emp e
    inner join emp m on e.manager_id = m.id
    where ...

    If anyone has a solution without client side programming, that would be great!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at:- http://sqllessons.com/categories.html
    granted its going top down and has a preset number of sub categories which mayu not hold for employee and reports to
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2016
    Posts
    2
    Thank you healdem for the link.

    This was helpful, even though not solving the problem fully, as I cannot know how deep the BOM might be (can be easily 3-7 levels).
    But I can continue with this. This was just so easy with Oracle :-)


    Mikuci

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Iirc it was said years ago you are unlikely to have mire than 5 or 6 levels in a BoM... Employees and line managers is different. But you could say wrie a query that joined the sane table working ip the hierarchy say 9 or 10 levels. If there is someone in the topmost level requery on that / those people. The only other way woul be a form of stored procedure that itetates uo the tree
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •