Archives of the TeradataForum

Message Posted: Mon, 30 Apr 2012 @ 15:51:11 GMT

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

Try a recursive query.....

     WITH RECURSIVE TEMP_TABLE
     (STRING_TXT, SUBSTR_TXT, START_POSITION, EXEC_CNT) AS (SELECT STRING_TXT
           , SUBSTR(STRING_TXT ,START_POSITION +1) AS SUBSTR_TXT
           , POSITION('/' IN STRING_TXT) AS START_POSITION
           , 0  (INTEGER)
       FROM ( SELECT
     '/SCM/EDW/APPL/DOWNLOAD/DAILY/CURRENT/DWI.CPC.IRA.RATNG.DAILY' AS
     STRING_TXT) X
     UNION ALL
     SELECT  STRING_TXT
           , SUBSTR(SUBSTR_TXT,START_POSITION_NEW+1)
           , POSITION('/' IN SUBSTR_TXT) AS START_POSITION_NEW
           , EXEC_CNT + 1  (INTEGER)
      FROM    TEMP_TABLE  G
     WHERE  EXEC_CNT < 15
       AND POSITION('/' IN SUBSTR_TXT) > 0
     )
     SELECT  B.STRING_TXT
           , B.SUBSTR_TXT
       FROM TEMP_TABLE B
      INNER JOIN
           (SELECT MAX(EXEC_CNT) AS EXEC_CNT
              FROM TEMP_TABLE
           ) A
        ON A.EXEC_CNT = B.EXEC_CNT

Paul De Pons
Architect
JM Service Center, LLC.

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