How to get the difference of two column values in db2?

[Arun R Pai]

I have a table which contains some records. The table structure is as below.

PROJECT HOST_NAME OS_SHORT DEVICE_ID NETWORK_IN_KB NETWORK_OUT_KB TIMESTAMP PK_X LOAD_TIMESTAMP IN_ROLLUP
SEC-INF-FTP01 UNIX eth0 22608781 31978987 10/21/2010 06:01:02 2010-10-21 19:36:15.593000 -1
SEC-INF-FTP01 UNIX eth0 22606471 31977643 10/21/2010 06:01:02 2010-10-21 19:36:15.593000 -1
SEC-INF-FTP01 UNIX eth0 22601741 31974849 10/21/2010 05:01:02 2010-10-21 19:36:15.593000 -1
SEC-INF-FTP01 UNIX eth0 22598323 31972335 10/21/2010 05:01:02 2010-10-21 19:36:15.593000 -1
SEC-INF-FTP01 UNIX eth0 22580101 31955295 10/21/2010 04:01:01 2010-10-21 19:36:15.593000 -1

I need to insert same records in to an another temp table with same structure, but the data should be like this;

PROJECT HOST_NAME OS_SHORT DEVICE_ID NETWORK_IN_KB NETWORK_OUT_KB TIMESTAMP PK_X LOAD_TIMESTAMP IN_ROLLUP
SEC-INF-FTP01 UNIX eth0 2310 1344 10/21/2010 06:01:02 2010-10-21 19:36:15.593000 -1
SEC-INF-FTP01 UNIX eth0 0 2794 10/21/2010 06:01:02 2010-10-21 19:36:15.593000 -1
SEC-INF-FTP01 UNIX eth0 3418 2514 10/21/2010 05:01:02 2010-10-21 19:36:15.593000 -1
SEC-INF-FTP01 UNIX eth0 18222 17040 10/21/2010 05:01:02 2010-10-21 19:36:15.593000 -1
SEC-INF-FTP01 UNIX eth0 22580101 31955295 10/21/2010 04:01:01 2010-10-21 19:36:15.593000 -1

Ie, the difference of values from the previous records for the columns NETWORK_IN_KB & NETWORK_OUT_KB need to be inserted in to temp column.

Right now i am using this cursor and its just inserting the same data in to the temp table, not the difference


set v_rec_count = 0 ; --

for v_cur11 as c11 cursor with hold for
select distinct upper(HOST_NAME ) c1, scope.find_time (timestamp) c2, max(NETWORK_IN_ KB) c3,41 as c4, 'NETWORK_IN_KB' as c5
from utilization_scm d_net where in_rollup=0 and OS_SHORT='UNIX' group by host_name,times tamp
except select HOST_NAME c1, valuetimestamp c2, instancevalue c3,LPRF_ID c4, LPRF_COLUMNNAME c5 from utilization_mas ter
do

insert into temp_utilizatio n_master(HOST_N AME,VALUETIMEST AMP,INSTANCEVAL UE,LPRF_ID,LPRF _COLUMNNAME)
values(v_cur5.c 1,v_cur5.c2,v_c ur5.c3,v_cur5.c 4,v_cur5.c5);--

set v_rec_count=v_r ec_count + 1;--
if mod(v_rec_count ,v_commit_frequ ency)=0 then
commit;--
end if;--
end for;--

Please advice some solution.

[Arun R Pai]

I will make it simple.. let the table structure be

Name age timestamp_loade d

Arun 25 04:01:01 2010-10-21
Arun 29 05:01:01 2010-10-21
Anish 30 03:01:01 2010-10-21
Anish 35 04:01:01 2010-10-21


There are two arun and two anish. i need to take the difference of these records and insert the same in to a temp table like this

Name age timestamp_loade d

Arun 25 04:01:01 2010-10-21
Arun 4 05:01:01 2010-10-21
Anish 30 03:01:01 2010-10-21
Anish 5 04:01:01 2010-10-21


Any suggestions.

[Sajeev N]

SELECT MIN(TableName) as TableName   
            , ID   
            , COL1   
            , COL2   
            , COL3   
FROM    
(    
     SELECT 'Table I' as TableName   
                  , A.ID   
                  , A.COL1   
                  , A.COL2   
                  , A.COL3   
     FROM creator1.tablename A    
     UNION ALL    
     SELECT 'Table II' as TableName   
                  , B.ID   
                  , B.COL1   
                  , B.COl2   
                  , B.COL3   
     FROM creator2.tablename B   
) TMPTBL    
GROUP BY    
   ID   
 , COL1   
 , COL2   
 , COL3    
HAVING COUNT(*) <> 2    
ORDER BY ID

Try this..