Archives of the TeradataForum

Message Posted: Thu, 29 Jun 2006 @ 09:50:11 GMT

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

Anomy,

How about a recursive query?

     CREATE SET TABLE mytable
          (
           col1 INTEGER,
           col2 INTEGER)
     PRIMARY INDEX ( col1 );

     insert into mytable(2,200);
     insert into mytable(6,10);
     insert into mytable(9,400);
     insert into mytable(13,60);

     WITH RECURSIVE r (col1, col2, col3) AS
     (
     SELECT 0, c.col2, c.col1 -1
     FROM mytable c
     WHERE c.col1 IN
      (
     SELECT MIN(col1)
     FROM mytable)
     UNION ALL
     SELECT a.col1, a.col2, b.col1 - 1
     FROM mytable a, mytable b
     WHERE b.col1 IN
      (
     SELECT MIN(col1)
     FROM mytable
     WHERE col1 > a.col1)
     UNION ALL
     SELECT d.col1, d.col2, d.col1
     FROM mytable d
     WHERE d.col1 IN
      (
     SELECT MAX(col1)FROM mytable)
     UNION  ALL
     SELECT rgen.col1+1, rgen.col2, rgen.col3
     FROM r rgen
     Where rgen.col1  < rgen.col3
     )

     SELECT DISTINCT col1, col2
     FROM r
     ORDER BY 1
     ;

Returns....

      0     200
      1     200
      2     200
      3     200
      4     200
      5     200
      6      10
      7      10
      8      10
      9     400
     10     400
     11     400
     12     400
     13      60

The DISTINCT in the final select prevents two rows with a 0 in col1 if your data has a 0 row in col1.

The query assumes the input does not have multiple col2 values for the same col1 value. etc.

Mark

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