Hi Svein

Your first query is something I might be looking for, as for the second that is a fine way to know the depth of the item being referred but for future use. Thanks for both of them

The first query generates the following error:
---------------------------
An IBPP error occurred.
---------------------------
*** IBPP::SQLException ***
Context: Statement::Fetch
Message: isc_dsql_fetch failed.

SQL Message : -693
Too many concurrent executions of the same request

Engine Code : 335544663
Engine Message :
Too many concurrent executions of the same request

---------------------------
OK
---------------------------

It seems that the record keeps on referring to itself on and on and does not traverse the heirarchy, please see below the actual query I ran for the above error. What it seems to do is it gets stuck on the first iItemID in the sGoodsRecvNoteItem that matches with the ones stored under the JobID. I have tried 2 diff. jobs one with a sinlge selection and otehr with multiple selections but it sticks to records from sGoodsRecvNoteItem for the first it finds in this table for eg. the returned set is

99, 99, 15
99, 99, 44
99, 99, 79
99, 99, 82
and it keeps on returning the same records again and again till it reaches 4100 records fetched and the error above (4100/4 = 1025 the recursion limit)

Please advise further and thanks again.

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) = '_2M60RJ9T4')
UNION ALL
SELECT H.iID, p2.iPID AS iPK FROM mItems p2
JOIN Hier H on p2.iPID = H.iPK)

SELECT Hier.iID AS iItemID, Hier.iPK AS iItemPK, sGRNItem.iID
FROM sGoodsRecvNoteItem sGRNItem
JOIN Hier
ON sGRNItem.iItemID = Hier.iID

--- In firebird-support@yahoogroups.com, Svein Erling Tysv�r <svein.erling.tysvaer@...> wrote:
>
> Hi Bhavbhuti!
>
> I don't quite understand your question, but I'll still guess two things that may help you.
>
> First attempt is just a minor modification to your query:
>
> 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 H.iID, p2.iPID AS iPK FROM mItems p2
> JOIN Hier H on p2.iPID = H.iPK)
> SELECT Hier.iPK, .....
>
> What I'm basically trying to do, is to keep iID the top level value of Hier at all levels of recursion (that of course means that the recursive join has to be towards iPK).
>
> If this is not what you want, then maybe adding the depth of the query may help you out. I'll modify the first query:
>
> WITH RECURSIVE Hier(iID, Depth) AS
> (SELECT p.iID, cast(1 as Integer) FROM mItems p WHERE p.iID IN (SELECT ID
> FROM gttItemsSelected
> WHERE TRIM(JobID) = ?{vp_JobID})
> UNION ALL
> SELECT p2.iID, H.Depth + 1 FROM mItems p2
> JOIN Hier H on p2.iPID = H.iID)
> SELECT H2.iID...
>
> As you can see, WITH RECURSIVE is quite powerful and can be tailored to do quite a lot of different things.
>
> HTH,
> Set
>
> --- In firebird-support@yahoogroups.com, "Bhavbhuti Nathwani" wrote:
> >
> > 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.
>