Solved

Pervasive - Recursive SQL

Posted on 2009-03-31
7
627 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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 500 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now