Hi Sergio, I've got good news for you - it is far easier to understand
(and try) recursive queries in Firebird than it is to understand (and
believe) any Big Bang theorem.

First, the query contains two parts - first something related to one or
more WITH [RECURSIVE] statements, and then the main query where you
treat the result of the WITH the same way you would treat a table or view.

Then, WITH is further split into at least two parts separated by UNION
ALL (it is possible to have several UNION ALL). The first part, before
UNION ALL, has to be an ordinary select statement, after UNION ALL,
normal rules apply to what you can SELECT (i.e. you have to select the
same number of rows that has to be of a similar type), but here you can
treat the result of the WITH statement as if it was a table, thereby
causing recursion.

Your actual WITH statement starts by finding the subcode for the code
you specify - i.e. all accounts that has summarizes_in = 1400 or
whatever. Then, the recursive UNION ALL finds all accounts for which the
subcode is a summary. Let's walk through 1400.

First, we select all codes that SUMMARIZES_IN 1400:
SUBCODE CODE
1410 1400
1420 1400

Then, we UNION this result and find all codes which SUMMARIZES_IN the
result, i.e. 1410 and 1420. Then we find the following:
SUBCODE CODE
1421 1420
1422 1420

Then, this recursive statement does the same for 1421 and 1422, but that
returns nothing since they are end nodes. Now, the total result contains
all subcodes that we need to summarize 1400, i.e. the main select is a
pretty simple SELECT SUM statement.

You could, of course, also have started in the other end when writing
this statement (it would be slower, but easier to understand and allow
you to select several totals at the same time):

WITH RECURSIVE CHARTS_SUMMARIZE
AS (SELECT CODE, MYFIELD
FROM CHARTS
UNION ALL
SELECT a.SUMMARIZES_IN, cs.MYFIELD
FROM CHARTS_SUMMARIZE cs
JOIN ACCOUNTS a ON cs.CODE = a.CODE)

SELECT CODE, sum(MYFIELD)
FROM CHARTS_SUMMARIZE
GROUP BY CODE

This first selects the entire CHARTS table, and then, recursively, the
same value (MYFIELD) for each of its ancestors.

I don't think you can get a recursive statement much simpler than this
one, but the concept of recursion isn't the simplest concept around...

HTH,
Set

Sergio H. Gonzalez wrote:

>> Assuming the first of your tables to be named CHARTS (OK, not the best
>> name) containing a field to sum named MyField and the latter, linking
>> table to be called ACCOUNTS, the following SQL should get you the sum
>> for the group you desire:
>>
>> WITH RECURSIVE GROUPCONTAINS(SUBCODE, CODE)
>> AS (SELECT A.CODE, A.SUMMARIZES_IN
>> FROM ACCOUNTS A
>> WHERE A.SUMMARIZES_IN = :CODE
>> UNION ALL
>> SELECT A.CODE, A.SUMMARIZES_IN
>> FROM GROUPCONTAINS G
>> JOIN ACCOUNTS A ON G.SUBCODE = A.SUMMARIZES_IN)
>>
>> SELECT SUM(C.MyField) FROM GROUPCONTAINS G
>> JOIN CHARTS C ON G.SUBCODE = C.CODE
>
>
> Thanks Svein!!!
>
> It does exactly what I need... the only problem is that I understand how it
> works, in the same way I undestand the Big Bang!! :) mmhh... I'd love to read
> some article about recursive sql with firebird... any chance you know where I
> can find it? I've tried with the documentation of FB, but couldn't find any
> tutor...
>
> Thanks a millon!!!
>
> -s