Sorry, I have no experience with FireBird and this query looks quite complex to me.

>Hi Naomi
>
>>For SQL Server 2005 and up it's a piece of cake.
>>
>>See recursive CTE.
>
>Thanks for the pointer, so does FireBird. FB 2.1 supports this, SQL-PT code:
>

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 2 AS iOrder, sBOMIItem.iID AS iPID, tBOMI.iID as iGID, tBOMI.tDt AS tDt,
>      Hier.iPK AS iItemID, CAST(0 AS DOUBLE PRECISION) AS bAdd, sBOMIItem.bQty AS bLess, '!' AS cSource,
>      'B.M.R. Issue no. ' || CASE WHEN tBOMI.cBK = '' THEN '' ELSE TRIM(tBOMI.cBK) || '/' END || TRIM(CAST(tBOMI.iNo AS CHARACTER(10))) || ' dtd. '
>         || CAST (EXTRACT(DAY from tBOMI.tDt) AS CHAR(2)) || '/' || CAST (EXTRACT(MONTH from tBOMI.tDt) AS CHAR(2)) || '/'
>         || CAST (EXTRACT(YEAR from tBOMI.tDt) AS CHAR(4)) AS mNarration
>   FROM tBOMIssue tBOMI
>      JOIN sBOMIssueItem sBOMIItem
>         ON sBOMIItem.iPID = tBOMI.iID
>      JOIN Hier
>         ON sBOMIItem.iItemID = Hier.iID
>WHERE tBOMI.tDt BETWEEN ?{ptFromDt} AND ?{ptToDt} ORDER BY tBOMI.tDt, tBOMI.cBk, tBOMI.iNo

>
>My only issue now is that the Hier.iID contains the actual IDs of the items. Heir.iPK can only have 2 levels of hierarchy return the same ID for reporting purposes, grand-child and lower will always return their respective parent's ID. Actually I was hoping that all the returned transactions have the same ID for the items so I could separate 2 or more top-most-level IDs for grouping / ordering purposes in the report. If you have any insights into this problem of mine please let me know.
>
>Kind regards.