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