Archives of the TeradataForum

Message Posted: Fri, 19 Jul 2002 @ 18:22:00 GMT

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

Claybourne,

You should be able to use a slightly modified version of Dieter N�th's SQL posted earlier.

I really like the way he uses the 'helper' table to break out the inlist into seperate items. Very handy indeed.

Give the following a try.

--Create Dieters helper table first.

CREATE volatile TABLE VT_Nums As (
SELECT
 day_of_calendar As N
FROM
 sys_calendar.calendar
WHERE
 day_of_calendar <= 64000 )
 WITH DATA
 ON COMMIT PRESERVE ROWS;

REPLACE MACRO TMP_DATES(inList varchar(64000))
As (

SELECT *
FROM sys_calendar.calendar c
WHERE c.calendar_date IN
 (
  SELECT
   CAST(
      SUBSTRING(:inList
          FROM n
      FOR (COALESCE((NULLIF(POSITION(','
                          IN SUBSTRING(:inList
          FROM n)),0)),64000)) - 1)
       AS int) AS StrPart
    FROM VT_Nums
    WHERE
      n BETWEEN 1
   AND Char_Length(:inList)
    AND
      (SUBSTRING(:inList
                  FROM n - 1 FOR 1) = ','
   OR
       n = 1) );
);

EXECUTE TMP_DATES('210402,1251227,330605,121004,1300403,101101');

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