Archives of the TeradataForum

Message Posted: Tue, 29 Oct 2002 @ 14:47:23 GMT

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

Yuval,

Try the following... (remember to change the Database names first)

This should work, AS LONG AS THE DIFFERENCE BETWEEN THE FROM_NUMBER AND THE TO_NUMBER is never greater than 73,414.

CREATE SET TABLE dncr8.tmp_tst_a,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      Index_Col CHAR(5),
      From_Number  INTEGER,
      To_Number INTEGER
)
PRIMARY INDEX  ( Index_Col)

CREATE SET TABLE dncr8.tmp_tst_b,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      Index_Col CHAR(5),
      Replic_No INTEGER,
      From_Number  INTEGER,
      To_Number INTEGER,
      Between_Number INTEGER
)
PRIMARY INDEX  ( Index_Col)

insert into dncr8.tmp_tst_a values ('a',1,20);
insert into dncr8.tmp_tst_a values ('b',21,30);
insert into dncr8.tmp_tst_a values ('c',31,50);

delete from dncr8.tmp_tst_b  all;
insert into dncr8.tmp_tst_b
SELECT
        b.index_col
        ,a.replic_no
        ,b.From_Number
        ,b.To_Number
        ,c.day_of_calendar       + b.from_number - 1 as Between_Number
FROM
        sys_calendar.calendar c,
        dncr8.tmp_tst_a b
JOIN
        (sel from_number,to_number,rank(from_number asc) as replic_No
from
dncr8.tmp_tst_a ) a
ON
        b.from_number = a.from_number
        and b.to_number = a.to_number
WHERE
        c.day_of_calendar + b.from_number - 1 >= b.from_number
        and c.day_of_calendar + b.from_number - 1 <= b.to_number;

Hope this helps,

Johannes

Johannes de Wet
UNUMProvident Corporation

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