Pervasive - Recursive SQL

Does anyone know if Pervasive does recursion?
I would like to use the 'WITH' clause for temporary tables.

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 ON MP.TOP_Code = SBL.SUB_Code
      )
      SELECT *
      fROM TR_MyParts
END ;
CALL MyTest3('BV221-10');
Can this be doine?
LVL 1
GeekHipsterAsked:
Who is Participating?
 
Bill BachConnect With a Mentor PresidentCommented:
I know the question title referred to recursion, but your original example, and all of our examples since, have not had any recursion in them.  I'm not sure that I completely understand what you are getting at.

As for the question of whether Pervasive can support recursion, the simple answer is YES.  All versions since PSQLV8.5 have supported a maximum recursion depth of 32.  Older versions had a recursion depth that was limited by memory -- when the engine crashed, you went too far.  They locked it down to a depth of 32 to align it with the Microsoft SQL Server limitation of the day.

You can test your version with a SP like this:

CREATE PROCEDURE TestRecursion(in :Depth integer) WITH DEFAULT HANDLER
AS BEGIN
      DECLARE :v1 CHAR(10);
      DECLARE :v2 CHAR(50);
      DECLARE :v3 CHAR(20);
      DECLARE :Result Integer;
      INSERT INTO DepthTest VALUES (:Depth);
      IF :Depth > 0 THEN
            CALL TestRecursion(:Depth - 1);
      END IF;
END;

Then, run "Call TestRecursion(32)" to verify that it works to 32 levels.

If you want to recursively build a BOM, then you should use an SP to create a temp table, then start a depth-first search for all sub-components (via a second SP), inserting them into the temp table as you go, using pure recursion with the CALL function.  When complete, you can return the results from the top level SP.
0
 
Bill BachPresidentCommented:
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 then do a SELECT INTO to build the temp table result set inside the stored procedure.

I'm not sure if Pervasive Postgres handles this or not...
0
 
GeekHipsterAuthor Commented:
I am using PSQL v9

I will try it and let you know.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
GeekHipsterAuthor Commented:
How would I use a stored procedure 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 ON MP.TOP_Code = SBL.SUB_Code AND
                        BSL.Top_Code = :PartNumber
      )
      SELECT *
      fROM TR_MyParts
END ;
CALL MyTest3('BV221-10');
0
 
GeekHipsterAuthor Commented:
DROP PROCEDURE MyTest3;
CREATE PROCEDURE MyTest3(IN :PartNumber CHAR(34)) AS
BEGIN
      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
                  MyTest3 AS MP ON MP.TOP_Code = SBL.SUB_Code AND
                        BSL.Top_Code = :PartNumber
      
END ;
CALL MyTest3('BV221-10');
0
 
Bill BachPresidentCommented:
That looks right. Obviously, I cannot test it without having access to the same database or creating a mock-up, but it looks right.

If you have issues for performance reasons, then you can 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
            FROM BOM_SUB_LEVEL  AS BSL JOIN
                  MyTest3 AS MP ON MP.TOP_Code = SBL.SUB_Code AND
                        BSL.Top_Code = :PartNumber;
SELECT * FROM #RT_MyParts;

If you want the data back in a specific order, then you can also optionally add a CREATE INDEX statement right before the SELECT to optimize the reading -- it may improve performance, or it may not, so I'd test it both ways.
0
 
GeekHipsterAuthor Commented:
The issue with this is that MyTest3 is a stored procedure and needs to call onto itself.
How can I do the following?

SELECT *
FROM MyProc( '123')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.