2 Replies Latest reply: Jul 3, 2013 10:43 PM by Khim Hoe Tan RSS

    How to Find Highest level in hierarchy

      Hi all,

       

      I have a table that contain a list of accounts in a hierarchical format. (for example, level 1 to 5) The account table looks like this:-

       

      Level 1
      Level 2Level 3Level 4Level 5
      1110011100111001100010000
      1120011200112001100010000
      1130011300113001100010000
      1210012100121001200010000
      1220012200122001200010000

       

      Level 1 is the lowest level and Level 5 is the highest. Level 1 is linked to the fact table. Now, my question is if I'm given an account number without knowing the level, how can I find the accounts of the lowest level?

       

      For example (Based on the above sample):

      If account 11000 is given, the accounts that should be returned are 11100, 11200, 11300.

      If account 10000 is given, the accounts that should be returned are 11100, 11200, 11300, 12100, 12200.

      If account 12100 is given, the accounts that should be returned are 12100.

       

      Some note:-

      - account number is unique

      - there is not pattern in the account number. All account numbers are randomly created.