Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Pervasive - Recursive SQL

Posted on 2009-03-31
7
Medium Priority
?
710 Views
Last Modified: 2012-05-06
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?
0
Comment
Question by:GeekHipster
  • 4
  • 3
7 Comments
 
LVL 28

Expert Comment

by:Bill Bach
ID: 24028783
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
 
LVL 1

Author Comment

by:GeekHipster
ID: 24028966
I am using PSQL v9

I will try it and let you know.
0
 
LVL 1

Author Comment

by:GeekHipster
ID: 24029023
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 1

Author Comment

by:GeekHipster
ID: 24029050
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
 
LVL 28

Expert Comment

by:Bill Bach
ID: 24029130
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
 
LVL 1

Author Comment

by:GeekHipster
ID: 24029591
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
 
LVL 28

Accepted Solution

by:
Bill Bach earned 2000 total points
ID: 24029848
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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question