Archives of the TeradataForum

Message Posted: Wed, 18 Sep 2003 @ 03:52:25 GMT

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

/* this approach pivots each row into 4 rows so the CASE statement
   can take the MAX.  This solution usually scales well because
   the "normalizer4" table is ususally dupicated on all amps and a
   LT/ST join takes place.   Put as many rows in normalizer table
   as there are "N" columns to take the MAX() on.
*/

.logon demotdat/dbc,dbc;

database financial;

drop table numrow;
create table numrow
 ( primk int
  ,amount1 dec(9,2)
  ,amount2 dec(9,2)
  ,amount3 dec(9,2)
  ,amount4 dec(9,2) )
primary index(primk) ;

/* setup test data to see if this really works */
insert numrow values (1, null, 32.00, 88.21, 43.35);
insert numrow values (2, 54.22, 18.76, null, 37.55);
insert numrow values (3, null, null   , null    ,123.33);
insert numrow values (4, 25.44, 1.10, 77.00, 0.88);
insert numrow values (5, -99.94, 43.33, 5280.00, 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,
       MAX(case r
       when 1 then amount1
       when 2 then amount2
       when 3 then amount3
       when 4 then amount4
    end) GREATEST_OF_N

from numrow
CROSS JOIN normalizer4

group by primk
order by primk;

 *** Query completed. 5 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

      primk  GREATEST_OF_N
-----------  -------------
          1          88.21
          2          54.22
          3         123.33
          4          77.00
          5        5280.00

--
Dwight Etheridge
Teradata Certified Master

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