Archives of the TeradataForum

Message Posted: Thu, 22 Jan 2004 @ 11:43:19 GMT

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

You'll need a helper table with consecutive numbers up to the maximum length of county_code in it.

I usually create it this way:

CREATE volatile TABLE vt_nums ( n INT)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt_nums VALUES(0);
INSERT INTO vt_nums VALUES(1);
INSERT INTO vt_nums VALUES(2);
INSERT INTO vt_nums VALUES(3);
INSERT INTO vt_nums VALUES(4);
INSERT INTO vt_nums VALUES(5);
INSERT INTO vt_nums VALUES(6);
INSERT INTO vt_nums VALUES(7);
INSERT INTO vt_nums VALUES(8);
INSERT INTO vt_nums VALUES(9);

CREATE TABLE nums(n INT NOT NULL PRIMARY KEY);

INSERT INTO nums
  SELECT
    i1.n +
    i2.n * 10 +
    i3.n * 100 +
    i4.n * 1000
  FROM
    vt_nums i1, vt_nums i2, vt_nums i3, vt_nums i4
;

DROP TABLE vt_nums;

COLLECT STATISTICS ON nums COLUMN n;

(You could also use sys_calendar.calendar.day_of_calendar instead)

Then it's a cross join:

SELECT
  state_code,
  SUBSTRING(county_code
    FROM n
    FOR  (POSITION(',' IN SUBSTRING(county_code || ',' FROM n))) - 1
   ) AS word
FROM tab s,
  (SELECT n FROM nums WHERE n BETWEEN 1 AND 100) tmp
WHERE
  (n <= CHAR_LENGTH(county_code) AND SUBSTRING(county_code FROM n - 1 FOR 1) = ',')
OR
  n = 1
ORDER BY state_code, n;

Dieter

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