|
|
Archives of the TeradataForum
Message Posted: Tue, 10 May 2011 @ 08:47:54 GMT
Subj: | | Re: Recursive Queries and Stored Procedures |
|
From: | | Simon Bloomer |
Dieter Noeth wrote:
| Recursion can usually be rewritten using some loops: WHILE/REPEAT and INSERTs into a Volatile Table within the SP. | |
| Or maybe you might be able to rewrite the logic using OLAP functions. | |
| What's the original query? | |
Dieter
The original query is basically what is shown below
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;
| |