Archives of the TeradataForum

Message Posted: Tue, 01 May 2012 @ 15:57:22 GMT

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

Although the recursive works and is probably more efficient here is another alternative using a product join checking the last 50 characters for a '/'.

Here the SQL is a bit more concise but requires the string have a '/' and a file name larger than 50 char. Of course that could be adjusted.

     SELECT  STRING_TXT
                     ,SUBSTR(STRING_TXT, (CHAR(STRING_TXT) - DAY_OF_CALENDAR + 1)
      ) FILE_NAME
       FROM ( SELECT
     '/SCM/EDW/APPL/DOWNLOAD/DAILY/CURRENT/DWI.CPC.IRA.RATNG.DAILY' AS
     STRING_TXT) X
     CROSS JOIN
                   SYS_CALENDAR.CALENDAR a
     WHERE DAY_OF_CALENDAR BETWEEN 1 AND 50
           AND  POSITION('/' IN SUBSTR(STRING_TXT, (CHAR(STRING_TXT) -
     DAY_OF_CALENDAR)  ) ) = 1
     QUALIFY  ROW_NUMBER()  OVER ( PARTITION BY STRING_TXT
                                             ORDER BY DAY_OF_CALENDAR )  = 1

Paul De Pons
Architect
JM Service Center, LLC.

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