Hi again!

Forget having a constant after UNION ALL, that will not work. But it might be that the join should be to the first table, I might have gotten that wrong. So try changing

WHERE p2.iPID = MyTree.iID

to

WHERE p2.iPID = p.iID

and see if that helps. If that fails, try

WITH RECURSIVE MyTree(iID) AS
(SELECT p.iID FROM mItems p WHERE p.iID = 62
UNION ALL
SELECT p2.iID FROM mItems p2
JOIN MyTree M on p2.iPID = M.iID)
SELECT p3.*
FROM sGoodsRecvNoteItem p3
JOIN MyTree m ON p3.iItemID = m.iID
ORDER BY iPID, iSrNo

If that fails as well, look at the release notes or wait until someone else answers or a few hours before I get the chance to get to a computer with Firebird 2.1 (to say that my memory isn't amongst the best Firebird 2.1 resources is an understatement).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Bhavbhuti Nathwani
Sent: 27. mars 2009 13:35
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Self-Joined Items Master and transactions stock ledger

Hi Svein

Thanks for your continue support. I tried your example but it fails on the line after UNION ALL at the place of MyTree.iID

SQL Message : -206
Column does not belong to referenced table

Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -206
Column unknown
MYTREE.IID
At line 4, column 53

on the other hand if I change the same MyTree.iID to 62 then I got one of my requirement fulfilled that is to include the parent (ie 62) records also without duplication that I had noticed in my way of doing it.

As for infinite recursion, still no joy.

Thanks and regards.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> Try
>
> WITH RECURSIVE MyTree(iID) AS
> (SELECT p.iID FROM mItems p WHERE p.iID = 62
> UNION ALL
> SELECT p2.iID FROM mItems p2 WHERE p2.iPID = MyTree.iID)
>
> SELECT p3.*
> FROM sGoodsRecvNoteItem p3
> JOIN MyTree m ON p3.iItemID = m.iID
> ORDER BY iPID, iSrNo