Hi Svein

Sorry to bother you again on this topic. Now I have come to another problem in this regards, I have the following WITH RECURSIVE:

WITH RECURSIVE Hier(iID) AS
(SELECT p.iID FROM mItems p WHERE p.iID IN (SELECT ID
FROM gttItemsSelected
WHERE TRIM(JobID) = ?{vp_JobID})
UNION ALL
SELECT p2.iID FROM mItems p2
JOIN Hier on p2.iPID = Hier.iID)
SELECT Hier.iID, .....

This is fine till I practically used the returned set for the stock ledger. As you will notice the Hier.iID contains the child IDs though they belong to the same top level ID. The report fell apart when it needed to distinguish 2 different top-level IDs, it got confused and separated all child, grand-child, too.

So I did the following:

WITH RECURSIVE Hier(iID, iPK) AS
(SELECT p.iID, p.iID AS iPK FROM mItems p WHERE p.iID IN (SELECT ID
FROM gttItemsSelected
WHERE TRIM(JobID) = ?{vp_JobID})
UNION ALL
SELECT p2.iID, p2.iPID AS iPK FROM mItems p2
JOIN Hier on p2.iPID = Hier.iID)
SELECT Hier.iPK, .....

This to an extent solved my problem, that is upto the second level of child; grand-child and lower return their own parent's ID.

Is there any way that the Hier(iID) returns the top-level ID only?

Thanks and regards.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> 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
>