Thanks. I think I have just found the problem. Svein your original query now updated on line 11 the last field needed to be n0

Kind regards
Bhavbhuti

WITH RECURSIVE MyCTE(iid, MyLevel, n0, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10) as
(SELECT lvl0.iid, cast(0 as Integer), lvl0.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)
, cast(null as Integer)
FROM mAccounts lvl0
WHERE lvl0.ipid = 0
UNION ALL
SELECT lvlc.iid, lvlp.MyLevel + 1, lvlp.n0
, case when lvlp.MyLevel = 0 then lvlc.iid else lvlp.n1 end
, case when lvlp.MyLevel = 1 then lvlc.iid else lvlp.n2 end
, case when lvlp.MyLevel = 2 then lvlc.iid else lvlp.n3 end
, case when lvlp.MyLevel = 3 then lvlc.iid else lvlp.n4 end
, case when lvlp.MyLevel = 4 then lvlc.iid else lvlp.n5 end
, case when lvlp.MyLevel = 5 then lvlc.iid else lvlp.n6 end
, case when lvlp.MyLevel = 6 then lvlc.iid else lvlp.n7 end
, case when lvlp.MyLevel = 7 then lvlc.iid else lvlp.n8 end
, case when lvlp.MyLevel = 8 then lvlc.iid else lvlp.n9 end
, case when lvlp.MyLevel = 9 then lvlc.iid else lvlp.n10 end
FROM MyCTE lvlp
JOIN mAccounts lvlc ON lvlp.iid = lvlc.ipid
WHERE lvlp.MyLevel < 10)

SELECT cte.n0, cte.n1, cte.n2, cte.n3, cte.n4, cte.n5, cte.n6, cte.n7, cte.n8, cte.n9, cte.n10, lvl.cName, cte.iid
FROM MyCTE cte
JOIN mAccounts lvl on cte.iid = lvl.iid

--- In firebird-support@yahoogroups.com, Venus Software Operations <venussoftop@...> wrote:
>
> 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
>