Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 15 Apr 2011 @ 09:49:49 GMT

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

Subj:   Re: Recursive Queries and Stored Procedures
From:   Usenko, Yuriy

  Recursive Queries have been supported on TD since TD2.6 (I believe).  


Then you need small example:

     database au;
     WITH RECURSIVE recq (id,data,iter,cdata) AS (  SELECT s0.id
       ,case when index(s0.data,',') = 0 then null    else SUBSTRING(s0.data
     FROM POSITION(',' IN s0.data)+1) end AS x_data
       ,1 AS x_iter
       ,case when index(s0.data,',') = 0 then s0.data else SUBSTRING(s0.data FROM 1 FOR
     POSITION(',' IN s0.data)-1) end AS x_cdata

      FROM source_tab AS s0
      WHERE s0.data is not null


      SELECT r0.id
       ,case when index(r0.data,',') = 0 then null    else SUBSTRING(r0.data
     FROM POSITION(',' IN r0.data)+1) end AS x_data
       ,r0.iter+1 AS x_iter
       ,case when index(r0.data,',') = 0 then r0.data else SUBSTRING(r0.data FROM 1 FOR
     POSITION(',' IN r0.data)-1) end AS x_cdata

      FROM recq AS r0
      WHERE r0.data is not null
      AND r0.iter < 250

     SELECT r1.id
     FROM recq AS r1
     order by 1,2

Yuriy Usenko.

EMEA GCC Prague.
Global Delivery Control Consultant.

  <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: 15 Jun 2023