Yes it helped thanks a lot Svein. It worked without a problem. What do you fear that might not work in a future version of FireBird? How large result set is large? Are a few thousand records fine as that is anticipated.

In the following piece
WITH RECURSIVE mAccountsOrdered(iID, iPID, iSrNo) AS
(SELECT iID
, iPID
, iSrNo
FROM mAccounts
ORDER BY iSrNo, iID)

I changed
ORDER BY iSrNo, iID)
to
ORDER BY iSrNo, iPID)
and
ORDER BY iSrNo)
but a quick look does not show any problem with the tree. Is this fine if I just use
ORDER BY iSrNo)
as I felt bit nervous if the iID or iPID were a factor for the ordering as accounts could be created in any order and any dependency on the iID being lower for a higher-up-in-the-tree-account.

Please advise

Thanks and regards
Bhavbhuti

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> >Thanks Svein for your reply which I have pasted below
> >
> >Yes, I have tried iLevel, iSrNo but the order all went wrong. A small eg. is as below:
> >
> >Present state:
> >iID, iPID, cName, iSrNo
> >1, 0, Balance Sheet, 3
> >2, 1, Assets, 1
> >3, 2, Fixed Assets, 1
> >4, 2, Current Assets, 2
> >5, 1, Liabilities, 2
> >6, 5, Current Liabilities, 1
> >7, 5, Duties Payable, 2
> >
> >Now user wants to modify iSrNo thus change the order for Assets and Liabilities as below: (also eg. Current and Fixed Assets)
> >1, 0, Balance Sheet, 3
> >5, 1, Liabilities, 1
> >6, 5, Current Liabilities, 1
> >7, 5, Duties Payable, 2
> >2, 1, Assets, 2
> >4, 2, Current Assets, 1
> >3, 2, Fixed Assets, 2
> >
> >I have also tried to add iSrNo with iLevel and iID in case that gave a better sort but no joy
>
> I see. Well, I have no idea whether this will work or not (and if it works, I wouldn't be certain it would continue to work when new version of Firebird appears), but try:
>
> WITH RECURSIVE mAccountsOrdered(iID, iPID, iSrNo) AS
> (SELECT iID, iPID, iSrNo
> FROM mAccounts
> ORDER BY iSrNo, iID),
>
> CTE(iID, iLevel, iSrNo, iTreeID01, iTreeID02,
> iTreeID03, iTreeID04, iTreeID05, iTreeID06, iTreeID07, iTreeID08,
> iTreeID09, iTreeID10) AS
> (SELECT LevelThis.iID
> , CAST(0 AS INTEGER)
> , LevelThis.iSrNo
> , LevelThis.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 mAccountsOrdered LevelThis
> WHERE LevelThis.iPID = 0
> UNION ALL
> SELECT LevelCurr.iID
> , LevelParent.iLevel + 1
> , LevelCurr.iSrNo
> , LevelParent.iTreeID01
> , CASE WHEN LevelParent.iLevel = 0 THEN LevelCurr.iID
> else LevelParent.iTreeID02 END
> , CASE WHEN LevelParent.iLevel = 1 THEN LevelCurr.iID
> else LevelParent.iTreeID03 END
> , CASE WHEN LevelParent.iLevel = 2 THEN LevelCurr.iID
> else LevelParent.iTreeID04 END
> , CASE WHEN LevelParent.iLevel = 3 THEN LevelCurr.iID
> else LevelParent.iTreeID05 END
> , CASE WHEN LevelParent.iLevel = 4 THEN LevelCurr.iID
> else LevelParent.iTreeID06 END
> , CASE WHEN LevelParent.iLevel = 5 THEN LevelCurr.iID
> else LevelParent.iTreeID07 END
> , CASE WHEN LevelParent.iLevel = 6 THEN LevelCurr.iID
> else LevelParent.iTreeID08 END
> , CASE WHEN LevelParent.iLevel = 7 THEN LevelCurr.iID
> else LevelParent.iTreeID09 END
> , CASE WHEN LevelParent.iLevel = 8 THEN LevelCurr.iID
> else LevelParent.iTreeID10 END
> FROM CTE LevelParent
> JOIN mAccountsOrdered LevelCurr
> ON LevelParent.iID = LevelCurr.iPID
> WHERE LevelParent.iLevel < 10)
>
> SELECT CTE.iID
> , CAST(CTE.iLevel + 1 AS INTEGER) AS iLevel
> , CTE.iSrNo
> , CTE.iTreeID01
> , CTE.iTreeID02
> , CTE.iTreeID03
> , CTE.iTreeID04
> , CTE.iTreeID05
> , CTE.iTreeID06
> , CTE.iTreeID07
> , CTE.iTreeID08
> , CTE.iTreeID09
> , CTE.iTreeID10
> , TRIM(mAccounts.cName)
> || CASE WHEN COALESCE(mAccounts.iCityID, 0) = 0 THEN ''
> ELSE ', ' || TRIM(mLookups.vValue) END AS cNameCity
> FROM CTE
> JOIN mAccounts
> ON CTE.iID = mAccounts.iID
> LEFT JOIN mLookups
> ON mLookups.iID = mAccounts.iCityID
>
> My wild thought (I've never tried) is that maybe prior ordering accidentally is partially preserved through a recursive CTE. Even if it gets the correct ordering, I fear that it might be slow on large result sets, so this is really a wild guess...
>
> HTH,
> Set
>