RE: A How-To

  • From: david wendelken <davewendelken@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 23 May 2005 13:03:48 -0400 (GMT-04:00)

Here's my test of Jeff's decode/max idea:

create table test
(record_key number
,id number
,value varchar2(10)
)
/

-- id 1 = name
insert into test values
(1,1,'garfield');

insert into test values
(2,1,'beetle');

insert into test values
(3,1,'dilbert');

insert into test values
(4,1,'funky');

-- id 2 = age

insert into test values
(1,2,30);

insert into test values
(2,2,60);

insert into test values
(3,2,20);

-- id 3 = weight
insert into test values
(1,3,15);

insert into test values
(2,3,150);

insert into test values
(3,3,240);

select record_key,
       max(decode(id,1,value,'')) name,
       max(decode(id,2,value,'')) age,
       max(decode(id,3,value,'')) weight
  from test
 group by record_key
/

RECORD_KEY NAME       AGE        WEIGHT
---------- ---------- ---------- ----------
         1 garfield   30         15
         2 beetle     60         150
         3 dilbert     20         240
         4 funky

It works if not all the ids have data.  

Won't work reliably if more than one value per id per employee is on file.
However, if you had a "is_most_current" column and maintained it 
programmatically, you could filter on that to get only the latest version of a 
field's value.  
Don't think a date-timestamp would work for this purpose, you would have to do 
a max of a max.  (I suspect the SQL would hurt our heads if it was workable. :)



--
//www.freelists.org/webpage/oracle-l

Other related posts: