Hi all

Svein had kindly provided me a previous query that gave me the path to
each record in a self-joined table. The query below seems to give me a
relative path of iIDs to the record, what I currently require is the
full path of iID for each record like following:

1, 0, 0, 0, 0, Manufacturing A/c., 1
1, 2, 0, 0, 0, Debit, 2
1, 2, 3, 0, 0, Purchase A/c., 3
1, 2, 3, 4, 0, Local Purchases A/c., 4
1, 2, 3, 5, 0, Out-State Purchases A/c., 5
1, 6, 0, 0, 0, Credit, 6
1, 6, 7, 0, 0, Sale A/c., 7
1, 6, 7, 8, 0, Trading Sale A/c., 8

Please advise.

Thanks and regards
Bhavbhuti



WITH RECURSIVE MyCTE(iID, MyLevel, ilvlID1, ilvlID2, ilvlID3, ilvlID4,
ilvlID5, ilvlID6, ilvlID7, ilvlID8, ilvlID9, ilvlID10) AS
(SELECT lvl1.iID, CAST(0 AS INTEGER), lvl1.iID, CAST(NULL AS INTEGER),
CAST(NULL AS INTEGER), CAST(NULL AS INTEGER), CAST(NULL AS
INTEGER),
CAST(NULL AS INTEGER), CAST(NULL AS INTEGER), CAST(NULL AS
INTEGER),
CAST(NULL AS INTEGER), CAST(NULL AS INTEGER)
FROM mAccounts lvl1
WHERE lvl1.ipid = 0
UNION ALL
SELECT lvlc.iID, lvlp.MyLevel + 1, lvlp.iID,
CASE WHEN lvlp.MyLevel = 0 THEN lvlc.iID ELSE lvlp.ilvlID2 END,
CASE WHEN lvlp.MyLevel = 1 THEN lvlc.iID ELSE lvlp.ilvlID3 END,
CASE WHEN lvlp.MyLevel = 2 THEN lvlc.iID ELSE lvlp.ilvlID4 END,
CASE WHEN lvlp.MyLevel = 3 THEN lvlc.iID ELSE lvlp.ilvlID5 END,
CASE WHEN lvlp.MyLevel = 4 THEN lvlc.iID ELSE lvlp.ilvlID6 END,
CASE WHEN lvlp.MyLevel = 5 THEN lvlc.iID ELSE lvlp.ilvlID7 END,
CASE WHEN lvlp.MyLevel = 6 THEN lvlc.iID ELSE lvlp.ilvlID8 END,
CASE WHEN lvlp.MyLevel = 7 THEN lvlc.iID ELSE lvlp.ilvlID9 END,
CASE WHEN lvlp.MyLevel = 8 THEN lvlc.iID ELSE lvlp.ilvlID10 END
FROM MyCTE lvlp
JOIN mAccounts lvlc
ON lvlp.iID = lvlc.ipid
WHERE lvlp.MyLevel < 10)

SELECT cte.ilvlID1, cte.ilvlID2, cte.ilvlID3, cte.ilvlID4, cte.ilvlID5,
cte.ilvlID6,
cte.ilvlID7, cte.ilvlID8, cte.ilvlID9, cte.ilvlID10, lvl.cName,
cte.iID
FROM MyCTE cte
JOIN mAccounts lvl
ON cte.iID = lvl.iID