Solved

Pervasive - Recursive SQL

Posted on 2009-03-31
7
694 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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…
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…

630 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