Archives of the TeradataForum

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

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

Yes.

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

      UNION ALL

      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
      ,r1.cdata
     FROM recq AS r1
     order by 1,2
     ;

Yuriy Usenko.

TERADATA CORPORATION
EMEA GCC Prague.
Global Delivery Control Consultant.

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