3 Replies Latest reply: Mar 26, 2012 12:21 PM by Przemysław Wojda RSS

    Hierarchy - how to concatenate tables with different depth nodes

    Przemysław Wojda

      Hi All,

       

      using below code I mean to get chart of accounts from several tables. All tables have identical structure, only node depth changes - all tables node depth is 3, one's node depth is 5. After reload, instead of desired one table Accounts I arrive with three tables: Accounts, Accounts-Expanded Nodes and $Syn. What could be done to get one table one?

       

       

      DBS:
      SELECT DB_Name FROM Configuration;
      
      Accounts:
      LET NoOfDBS = NoOfRows('DBS');
      FOR i=0 TO $(NoOfDBS)-1
      LET DB_Name = peek('DB_Name',$(i),'DBS');
      HIERARCHY(Acc_AccId, Acc_ParId, Acc_Name)
      SELECT 
                '$(DB_Name)' AS DB_Name, 
                Acc_Type, 
                Acc_Name,
                Acc_AccId, 
                Acc_ParId
      FROM CDN_$(DB_Name).CDN.Accounts;
      NEXT
      
      

       

      Regards,

      Przemek

        • Re: Hierarchy - how to concatenate tables with different depth nodes
          Jakub Michalik

          I am not sure why it ends like that - Accounts-Expanded Nodes is an automatic table created during hierarchy load and shoud get auto-dropped, but probably something goes wrong because of the loop. I would first load the whole adjacent node table (via the loop) and then reload it using hierarchy load:

           

          DBS:
          SELECT DB_Name FROM Configuration;
          
          AccountsAdjacent:
          LET NoOfDBS = NoOfRows('DBS');
          FOR i=0 TO $(NoOfDBS)-1
          LET DB_Name = peek('DB_Name',$(i),'DBS');
          SELECT
                    '$(DB_Name)' AS DB_Name,
                    Acc_Type,
                    Acc_Name,
                    Acc_AccId,
                    Acc_ParId
          FROM CDN_$(DB_Name).CDN.Accounts;
          NEXT
          
          Accounts:
          HIERARCHY(Acc_AccId, Acc_ParId, Acc_Name)
          LOAD * 
          RESIDENT AccountsAdjacent;
          
          DROP TABLE AccountsAdjacent;