Archives of the TeradataForum

Message Posted: Thu, 15 Oct 2009 @ 18:31:26 GMT

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

Hi Kumar,

it's not WITH - INSERT - SELECT but INSERT - WITH SELECT:

     INSERT INTO MYDATABASE.MYTEMPTABLE
     WITH RECURSIVE RECT(sno,resttext,no_of_element) AS
            (SELECT ROOT.SNO, root.TEXT, 1
             FROM MYDATABASE.MYSAMPLEDATA ROOT
             UNION ALL
             SELECT
              PARENT.SNO,
              SUBSTR(PARENT.resttext,
                     POSITION(',' IN PARENT.resttext)+1,
                     CHARACTERS(TRIM(PARENT.resttext))
                     ) AS resttext
              ,no_of_element+1
             FROM
               RECT PARENT
             WHERE POSITION(',' IN PARENT.resttext)<>0
           )
           SELECT  CASE WHEN POSITION(',' IN resttext)<>0
                 THEN SUBSTR(resttext,
                    1,
                    POSITION(',' IN resttext)-1
                    )
                 ELSE
                    resttext
                 END AS parttext
           FROM RECT     ;

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