Hi Ain!

You need a JOIN in the unioned query. Here's a quick suggestion that will not work, but it might have had D and AM been CHARACTER fields:

WITH RECURSIVE
Tree AS(
SELECT TT1.UID, TT1.Parent, TT1.D, TT1.AM, TT1.D ||'.'|| TT1.AM AS MyOrderBy
FROM TAB_Tree TT1
WHERE TT1.D BETWEEN 100 AND 200
AND TT1.Parent is null
UNION ALL
SELECT TT2.UID, TT2.Parent, TT2.D, TT2.AM, TT1.MyOrderBy ||'.'|| TT2.AM
FROM Tree T
JOIN TAB_Tree TT2 ON T.UID = TT2.Parent
)
SELECT UID, Parent, D, AM FROM Tree
ORDER BY MyOrderBy

Note that I rarely use Fb2 (and hence CTEs, in particular recursive CTEs), and would be surprised if this worked at all.

HTH,
Set

--- In firebird-support@yahoogroups.com, "ainpoissee" <ainpoissee@...> wrote:
>
> --- In firebird-support@yahoogroups.com, "ainpoissee" <ainpoissee@> wrote:
> >
> > and I want to load it into memory in preorder order (root first, then each node is immediately followed by it's childs). As the depth of the tree is unknown I can't use ie join approach, but CTE should fit the bill, right?
> > Childs at the same level (it's not a binary tree, number of childs at each level is not limited) must be odered by AM field. Root nodes (Parent = NULL) should be ordered first by D then by AM. D is also the main "filter field" ie the table is queryed for a range of D. I have not worked with CTE before so I'd like to get some help as how to set this query up... this is what I come up with:
> >
> > WITH RECURSIVE
> > Tree AS(
> > SELECT UID, Parent, D, AM FROM TAB_Tree
> > WHERE(D >= 100)AND(D <= 200)
> > UNION ALL
> > SELECT T.UID, T.Parent, T.D, T.AM FROM Tree T WHERE(Parent = T.UID)
> > )
> > SELECT UID, Parent, D, AM FROM Tree
> > ORDER BY D, AM, Parent NULLS FIRST
> >
> > But I'm not quite sure is it right...
>
> Well, it isn't right for sure - in case the child having AM smaller than parent it will be sorted before it's parent, not after... can anyone come up with a query which returns tree in pre- or postorder order?
>
>
> ain
>