Archives of the TeradataForum

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

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

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