Archives of the TeradataForum

Message Posted: Wed, 02 Jul 2003 @ 21:22:25 GMT

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

Here is one technique. Cross join the data table to a "normalizer" table to get row proliferation. Then the MIN/Group by function can choose the earliest non-null date using MIN(). The number of rows to stock in the "normalizer" table will reflect how many date fields to choose among in the data row. In my code below, I picked among 4 dates, thus the normalizer4, with 4 rows.

The optimizer will normally duplicate the small normalizer table to all amps and do all this in parallel, usually a fast operation.

create table daterow
 ( primk int
  ,date1 DATE
  ,date2 DATE
  ,date3 DATE
  ,date4 DATE )
primary index(primk);

insert daterow values (1, null, '2001-02-14', '2002-05-16', '1995-12-12');
insert daterow values (2, '2003-04-11', '1990-12-11', null, '1980-01-03');
insert daterow values (3, null, null   , null    ,'1934-12-10');
insert daterow values (4, null, null, '2003-07-02', null);
insert daterow values (5, null, '1902-11-05', '1902-11-04', null);

create volatile table normalizer4
 ( r byteint) on commit preserve rows;

insert normalizer4 values (1);
insert normalizer4 values (2);
insert normalizer4 values (3);
insert normalizer4 values (4);

select primk,
       MIN(case r
       when 1 then date1
       when 2 then date2
       when 3 then date3
       when 4 then date4
    end) as min_non_null_date
from daterow
CROSS JOIN normalizer4
group by primk
order by primk;

result set:

primk   min_non_null_date
1       1995-12-12
2       1980-01-03
3       1934-12-10
4       2003-07-02
5       1902-11-04

--
Dwight Etheridge
Teradata Certified Master

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