Solved

Pervasive - Recursive SQL

Posted on 2009-03-31
7
681 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to remove error in database 6 55
Append Query Access 2010 4 63
How to combine two oracle database together 9 75
Any benefit to adding a Clustered index here? 4 39
Creating and Managing Databases with phpMyAdmin in cPanel.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…

738 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