Stored Procedures can't handle recursive queries

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Enthusiast

Stored Procedures can't handle recursive queries

I have been trying to translate this SP at http://hansolav.net/sql/graphs.html#dijkstra from T-SQL to Teradata SQL. I've gotten most of it done apart from the recursive query. I understand that Teradata SPs can't handle recursive queries. Does anyone know how to re-write recursive query (red text) to make this SP work?

 

We are currently using TD 16.10


 CREATE VOLATILE TABLE VT_Nodes
 (
  Id INT,    -- The Node Id
  Estimate DECIMAL(10,3) ,    -- What is the distance to this node, so far?
  Predecessor INT,    -- The node we came from to get to this node with this distance.
  Done SMALLINT        -- Are we done with this node yet (is the estimate the final distance)?
 )PRIMARY INDEX(id)
 ON COMMIT PRESERVE ROWS;

 


CREATE PROCEDURE db.usp_Dijkstra
(
StartNode INT
, EndNode INT /*= NULL*/
)
SQL SECURITY INVOKER
BEGIN
/*    -- Automatically rollback the transaction if something goes wrong.   
    SET XACT_ABORT ON   
    BEGIN TRAN
    */
 -- Increase performance and do not intefere with the results.
  --SET NOCOUNT ON;
 
    DECLARE FromNode INT;
  DECLARE CurrentEstimate INT;
 
    -- Fill the temporary table with initial data
    INSERT INTO VT_Nodes (Id, Estimate, Predecessor, Done)
    SELECT Id, 9999999.999, NULL, 0 FROM db.T_NODE;
   
    -- Set the estimate for the node we start in to be 0.
    UPDATE VT_Nodes SET Estimate = 0 WHERE Id =StartNode;
/*    IF @@rowcount <> 1
    BEGIN
        DROP TABLE VT_Nodes
        RAISERROR ('Could not set start node', 11, 1)
        ROLLBACK TRAN       
        RETURN 1
    END*/

 

    -- Run the algorithm until we decide that we are finished
 label1:
    WHILE (1 = 1)  DO
 BEGIN
        -- Reset the variable, so we can detect getting no records in the next step.
        SET FromNode = NULL;

        -- Select the Id and current estimate for a node not done, with the lowest estimate.
  SET FromNode = (SELECT TOP 1 Id  FROM VT_Nodes WHERE Done = 0 AND Estimate < 9999999.999 ORDER BY Estimate);
  SET CurrentEstimate  = (SELECT TOP 1 Estimate FROM VT_Nodes WHERE Done = 0 AND Estimate < 9999999.999 ORDER BY Estimate);
       
        -- Stop if we have no more unvisited, reachable nodes.
        IF FromNode IS NULL OR FromNode = EndNode THEN
   LEAVE label1;
  END IF;

        -- We are now done with this node.
        UPDATE VT_Nodes
  SET Done = 1
  WHERE Id = FromNode;

        -- Update the estimates to all neighbour node of this one (all the nodes
        -- there are edges to from this node). Only update the estimate if the new
        -- proposal (to go via the current node) is better (lower).
  
    UPDATE VT_Nodes
    FROM db.T_Edge AS e
   SET Estimate = CurrentEstimate + e.Weight
   , Predecessor = FromNode
   WHERE VT_Nodes.Id = e.ToNode
   AND e.FromNode = FromNode
   AND (CurrentEstimate + e.Weight) < VT_Nodes.Estimate
   AND Done = 0
       ;
 END ;
 END WHILE label1;

DECLARE  CUR1 CURSOR WITH RETURN ONLY TO CLIENT FOR
 -- Select the results. We use a recursive common table expression to
 -- get the full path from the start node to the current node.
 WITH RECURSIVE BacktraceCTE(Id, nm, Distance, PATH, NamePath)
 AS
 (
  -- Anchor/base member of the recursion, this selects the start node.
  SELECT n.Id
  , nd.nm
  , n.Estimate
  , CAST(n.Id AS VARCHAR(8000)) AS PATH
  ,CAST(nd.nm AS VARCHAR(8000)) AS NamePath
  FROM VT_Nodes AS n
  JOIN db.T_NODE AS nd
  ON n.Id = nd.Id
  WHERE n.Id = StartNode
  
  UNION ALL
  
  -- Recursive member, select all the nodes which have the previous
  -- one as their predecessor. Concat the paths together.
  SELECT n.Id
  , nd.nm
  , n.Estimate
  ,CAST((cte.PATH || ',' ||TRIM(n.Id)) AS VARCHAR(8000))
  ,CAST((cte.NamePath || ',' || nd.nm) AS VARCHAR(8000))
  FROM VT_Nodes n
  JOIN BacktraceCTE AS  cte
  ON n.Predecessor = cte.Id
  JOIN db.T_NODE AS  nd
  ON n.Id = nd.Id
 )
 SELECT Id
 , nm
 , Distance
 , PATH
 , NamePath
 FROM BacktraceCTE
 WHERE (Id = EndNode OR EndNode IS NULL) -- This kind of where clause can potentially produce
 ORDER BY Id        -- a bad execution plan, but I use it for simplicity here.
 ;
 
 OPEN CUR1;
 
    DROP TABLE VT_Nodes;
/*    COMMIT TRAN
    RETURN 0*/
END ;

 

 

 

Tables

CREATE TABLE DB.T_NODE
(
    Id INT
    ,Nm VARCHAR(50)
)PRIMARY INDEX(id)
;


CREATE TABLE DB.T_Edge
(
    FromNode INT ,
    ToNode INT,
   Weight DECIMAL (10, 3)
) PRIMARY INDEX(FromNode ,ToNode)
;

 

 

 

DATA
INSERT DB.T_NODE (Id, Nm) VALUES (1, 'Seattle');
INSERT DB.T_NODE (Id, Nm) VALUES (2, 'San Francisco');
INSERT DB.T_NODE (Id, Nm) VALUES (3, 'Las Vegas');
INSERT DB.T_NODE (Id, Nm) VALUES (4, 'Los Angeles');
INSERT DB.T_NODE (Id, Nm) VALUES (5, 'Denver');
INSERT DB.T_NODE (Id, Nm) VALUES (6, 'Minneapolis');
INSERT DB.T_NODE (Id, Nm) VALUES (7, 'Dallas');
INSERT DB.T_NODE (Id, Nm) VALUES (8, 'Chicago');
INSERT DB.T_NODE (Id, Nm) VALUES (9, 'Washington DC');
INSERT DB.T_NODE (Id, Nm) VALUES (10, 'Boston');
INSERT DB.T_NODE (Id, Nm) VALUES (11, 'New York');
INSERT DB.T_NODE (Id, Nm) VALUES (12, 'Miami');

 

 

INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (1, 2, 1306.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (1, 5, 2161.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (1, 6, 2661.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (2, 1, 1306.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (2, 3, 919.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (2, 4, 629.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (3, 2, 919.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (3, 4, 435.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (3, 5, 1225.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (3, 7, 1983.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (4, 2, 629.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (4, 3, 435.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (5, 1, 2161.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (5, 3, 1225.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (5, 6, 1483.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (5, 7, 1258.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (6, 1, 2661.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (6, 5, 1483.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (6, 7, 1532.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (6, 8, 661.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (7, 3, 1983.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (7, 5, 1258.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (7, 6, 1532.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (7, 9, 2113.000);
INSERT DB.T_Edge (FromNode, ToNode, Weight) VALUES (7, 12, 2161.000);