Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    33

    Unanswered: Find out the level in a hierarchy oracle 9i

    Hi All,

    I am using Oracle 9.2.0. I have a query regarding how to find out the level in a hierarchy.
    Hierarchy is

    Sector
    SectorID
    TUnit
    TUnitID
    Account
    AccountID
    Customer
    CustomerID
    Wholesaler
    WholesalerID

    I have got CustomerID from the application. I would like to know at what level this id exists in the hierarchy using sql query.
    Any Predefined functions in oracle to find out the level for a given id?
    Or how to go about it..

    Thanks in advance.

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    LEVEL is a pseudo-column that is provided with hierarchical queries... so, in your case all you would have to do is query/display/restrict the CustomerId column based on LEVEL.

    So, example:
    Code:
      SELECT
         LEVEL, parent_name, dept_name
      FROM
         myTable
      CONNECT BY
         prior dept_name = parent_name
      START WITH
         dept_name = 'Root Parent'
      ORDER BY LEVEL;
    JoeB

Posting Permissions

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