Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 14 Apr 2011 @ 11:42:59 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Recursive Queries and Stored Procedures
 
From:   Simon Bloomer

Hi all

We have a situation where we need to integrate to SAP using stored procedures. However one of these SP s would require a recursive query, which I understand is not supported (at least not on Teradata 12).

The query being used looks something like this

     WITH RECURSIVE HISTORY (CURRNT, PREVIOUS, Depth) AS
       (SELECT TICKET_ID,
               Withdrawn_Ticket_Airline_Cd||
               SUBSTR(Withdrawn_Ticket_Num,01,10)||
               SUBSTR(Withdrawn_Ticket_Issue_Dt,01,04)||
               SUBSTR(Withdrawn_Ticket_Issue_Dt,06,02)||
               SUBSTR(Withdrawn_Ticket_Issue_Dt,09,02), 0
           FROM workdb.dave_temp
               WHERE TICKET_ID = :DTE
        UNION ALL
        SELECT HISTORY.CURRNT, Withdrawn_Ticket_Airline_Cd||
               SUBSTR(Withdrawn_Ticket_Num,01,10)||
               SUBSTR(Withdrawn_Ticket_Issue_Dt,01,04)||
               SUBSTR(Withdrawn_Ticket_Issue_Dt,06,02)||
               SUBSTR(Withdrawn_Ticket_Issue_Dt,09,02), HISTORY.Depth + 1
          FROM HISTORY INNER JOIN workdb.sb_temp
                  ON HISTORY.PREVIOUS = workdb.sb_temp.TICKET_ID
                 AND HISTORY.Depth < 99)
     SELECT * FROM HISTORY
     ORDER BY DEPTH;

Is there another way to achieving this?

Simon Bloomer



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016