Thanks for your response Karol. I guess I have to this way as I need the structure for printing the Balance Sheet purposes. As then I need to add the current balances and then generate the printout preview.

For the UI purposes I have a TreeView ActiveX which works fine with this added iSrNo field, but then the TV is generated in bits and pieces as and when the + sign is clicked.

I hope this was your question which I answered.

Kind regards
Bhavbhuti

--- In firebird-support@yahoogroups.com, liviuslivius@... wrote:
>
> Hi,
> do you really need to do this by sql?
> or only you need to show tree view for user or this is "table view of tree sort"?
> If you only need to show tree for user and user need sort this tree then sort on client side that tree not by query
> Karol Bieniaszewski
> firebird-support@yahoogroups.com napisał(a):
> Please bear with me, moved from FB General to here
> Hi all
> Previously Svein had kindly provided me with a CTE that will self join
> my Accounts master table in a tree. In itself it is working fine. Now
> the user wants to order the placement of the records a per their wish
> and not the natural order the records were originally created in. To
> this end I have added an iSrNo field that contains the order defined by
> the user. So all records at the same iLevel within a branch of a tree
> can have an iSrNo specified and they sort themselves and their children
> accordingly.
> Below is the adapted CTE that was provided to me before with the new
> iSrNo field added. I have tried a few ORDER BY myself but that ruined
> the tree compeltely.
> If someone would kindly suggest a way for me to getting the tree as per
> users iSrNo (for that iLevel) it would be great.
> Please advise.
> Kind regards
> Bhavbhuti
> WITH RECURSIVE 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 mAccounts 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 mAccounts 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
>
>
> [Non-text portions of this message have been removed]
>