>Hi,
>
>On page 27 ("Examples of recursive table expressions" of FireBird 2.1 -- What's New), there is an example (indenting the
>query results depending on the level) but it's not clear to me whether I must use a column in my tree-structure to keep >track of the indent level of the record.
>
>Is it possible to pick up the level of a record in a tree-structure in order to indent the query results depending on the >level?

I don't think there is any inherent level, so you probably need to keep track of the level yourself (I might be wrong, if so I hope someone will correct). However, I think the below might work if you prefer to keep track of the level rather than the indentation (I haven't tried it):

WITH RECURSIVE
DEPT_YEAR_BUDGET AS
(
SELECT FISCAL_YEAR, DEPT_NO,
SUM(PROJECTED_BUDGET) AS BUDGET
FROM PROJ_DEPT_BUDGET
GROUP BY FISCAL_YEAR, DEPT_NO
),
DEPT_TREE AS
(
SELECT DEPT_NO, HEAD_DEPT, DEPARTMENT,
CAST(0 AS INTEGER) AS LEVEL
FROM DEPARTMENT
WHERE HEAD_DEPT IS NULL
UNION ALL
SELECT D.DEPT_NO, D.HEAD_DEPT, D.DEPARTMENT,
H.LEVEL+1
FROM DEPARTMENT D JOIN DEPT_TREE H
ON D.HEAD_DEPT = H.DEPT_NO
)

SELECT D.DEPT_NO,
SUBSTRING CAST((CAST('' AS CHAR(255)) FROM 1 FOR D.LEVEL) || D.DEPARTMENT AS VARCHAR(255)) AS DEPARTMENT,
B_1993.BUDGET AS B_1993, B_1994.BUDGET AS B_1994,
B_1995.BUDGET AS B_1995, B_1996.BUDGET AS B_1996
FROM DEPT_TREE D
LEFT JOIN DEPT_YEAR_BUDGET B_1993
ON D.DEPT_NO = B_1993.DEPT_NO
AND B_1993.FISCAL_YEAR = 1993
LEFT JOIN DEPT_YEAR_BUDGET B_1994
ON D.DEPT_NO = B_1994.DEPT_NO
AND B_1994.FISCAL_YEAR = 1994
LEFT JOIN DEPT_YEAR_BUDGET B_1995
ON D.DEPT_NO = B_1995.DEPT_NO
AND B_1995.FISCAL_YEAR = 1995
LEFT JOIN DEPT_YEAR_BUDGET B_1996
ON D.DEPT_NO = B_1996.DEPT_NO
AND B_1996.FISCAL_YEAR = 1996

HTH,
Set