Hierarchy - how to concatenate tables with different depth nodes

[#1]

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

[kuba_michalik · Accepted solution]

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;

[hic]

The Hierarchy prefix makes a transformation that needs the entire Adjacent Nodes table - an output record from this transformation has information from many of the input records. So, concatenation of two different Hierarchy prefix statements will lead to unpredictable results.

So, Kuba's solution is completely right. First, create the entire Adjacent Nodes table, then do the Hierarchy transformation.

/HIC

[#4]

Thank you, Kuba, thank you Henric. It did the job. One thing for future readers, though - when doing hierarchy transformation join account and parent id with db, otherwise hierarchy will go wild.