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.