RE: A How-To

From: david wendelken <davewendelken_at_earthlink.net>
Date: Mon, 23 May 2005 13:03:48 -0400 (GMT-04:00)
Message-ID: <25240366.1116867828111.JavaMail.root@wamui-rubis.atl.sa.earthlink.net>

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. :)

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

Received on Mon May 23 2005 - 13:08:33 CDT