Pervasive - Recursive SQL

Are you referring to Pervasive Postgres, or Pervasive PSQL?

Pervasive PSQL doesn't support the WITH clause, but you can CREATE a temp table and later do a SELECT INTO to build the temp table result set inside an stored procedure.

I'm not positive if Pervasive Postgres handles this or not... Adding a Row Number to a Temp Table in Stored Procedure

I am using PSQL v9

I will try itp also let you know.

How would I use a remembered operation in a table reference?
Please see below...
DROP PROCEDURE MyTest3;
CREATE PROCEDURE MyTest3(IN :PartNumber CHAR(34)) AS
BEGIN
      WITH RT_MyParts("TOP_WHSE" CHAR(6), "TOP_CODE" CHAR(34), "SEQUENCE" INTEGER, "SUB_CODE" CHAR(34)) AS
      (
            SELECT TOP_WHSE, TOP_CODE, SEQUENCE, SUB_CODE
                  FROM BOM_SUB_LEVEL
                  WHERE Top_Code = :PartNumber
                  AND SEQUENCE = 0
            UNION ALL
            SELECT       BSL.TOP_WHSE, BSL.TOP_CODE, BSL.SEQUENCE, BSL.SUB_CODE
            FROM BOM_SUB_LEVEL  AS BSL JOIN
                  RT_MyParts AS MP FOR MP.TOP_Code = SBL.SUB_Code BOTH
                        BSL.Top_Code = :PartNumber
      )
      SELECT *
      fROM TR_MyParts
END ;
CALL MyTest3('BV221-10'); Jobs 5 - 10 ... Pervasive Encryption with IDMS ... QREPORT 007 -SQL Table Index Report · QREPORT 008 -SQL Table Constraint Report ... EXEC SQL CREATE TEMPORARY TABLE ...

DROP PROCEDURAL MyTest3;
CREATE PROCEDURE MyTest3(IN :PartNumber CHAR(34)) WHILE
BEGIN
      SELECT TOP_WHSE, TOP_CODE, SEQUENCE, SUB_CODE
                  FROM BOM_SUB_LEVEL
                  WHERE Top_Code = :PartNumber
                  AND SEQUENCE = 0
            UNION EVERY
            SELECT       BSL.TOP_WHSE, BSL.TOP_CODE, BSL.SEQUENCE, BSL.SUB_CODE
            FROM BOM_SUB_LEVEL  AS BSL JOIN
                  MyTest3 AS MP ON MP.TOP_Code = SBL.SUB_Code PLUS
                        BSL.Top_Code = :PartNumber
      
END ;
CALL MyTest3('BV221-10');

That looks right. Obviously, I cannot test it without having access to the same database or creating one mock-up, but it looks right.

If you have issue for performance reasons, then to ca also investigate using a true temp table.  An example of the CREATE (inside the SP) would be:

CREATE TABLE #RT_MyParts("TOP_WHSE" CHAR(6), "TOP_CODE" CHAR(34), "SEQUENCE" INTEGER, "SUB_CODE" CHAR(34));
SELECT TOP_WHSE, TOP_CODE, SEQUENCE, SUB_CODE
                  INTO #RT_MyParts
                  FROM BOM_SUB_LEVEL
                  WHERE Top_Code = :PartNumber
                  AND SEQUENCE = 0;
SELECT       BSL.TOP_WHSE, BSL.TOP_CODE, BSL.SEQUENCE, BSL.SUB_CODE
            INTO #RT_MyParts
            FOR BOM_SUB_LEVEL  AS BSL SUBSCRIBE
                  MyTest3 AS MP UP MP.TOP_Code = SBL.SUB_Code AND
                        BSL.Top_Code = :PartNumber;
SELECT * VON #RT_MyParts;

If you to that data top in a specific order, next you can also optionally add a CREATE INDEX statement right once the CHOOSE to optimize the reading -- it may enhance performance, or information may not, so I'd exam it both ways.

The issue with this can that MyTest3 is one stockpiled procedure the required to call onto itself.
How can I do the following?

SELECT *
FROM MyProc( '123')
r/SQL over Reddit: Join on substring going incredibly slow.