Home » SQL & PL/SQL » SQL & PL/SQL » Comparing records and merging them into one record (merged)
Comparing records and merging them into one record (merged) [message #209408] Thu, 14 December 2006 12:10 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

ITEM    MTH       COL1     COL2        COL3         COL4

I1    200605       10       20          30           40
I1    200604       10       20          30           40
I1    200603       10       20          31           40
I1    200602       10       20          30           40
I1    200601       10       20          30           40




The above row needs to be converted like this..


ITEM   EFF_FROM      COL1    COL2    COL3    COL4  END_IRM
I1     200604         10      20      30      40   200605
I1     200603         10      20      31      40   200603
I1     200601         10      20      30      40   200602




basically i want to compare the consecutive rows to see if there is a change in value in columns col1 to col4.the idea is to combine the records for which all 4 columns are same into a eff_from and eff_to.If this can be easily done in a pl/sql, please let me know how to proceed.
Thanks
Re: sql query to compare records [message #209428 is a reply to message #209408] Thu, 14 December 2006 15:09 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
> i want to compare the consecutive rows
There is no such concept as "consecutive rows" in any RDBMS.
In Set Theory you have a collection of data and order is indeterminate.
Re: sql query to compare records [message #209436 is a reply to message #209428] Thu, 14 December 2006 15:46 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
sorry.. i should have said for a particular item, previous month and the current month information in all the four columns.
Re: sql query to compare records [message #209603 is a reply to message #209408] Fri, 15 December 2006 16:31 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


bspdb>@c:\sriwork\srini.sql;
bspdb>drop table foo;

Table dropped.

bspdb>create table foo (
2 ITEM varchar2(2) ,
3 MTH date ,
4 COL1 number(2) ,
5 COL2 number(2) ,
6 COL3 number(2),
7 COL4 number(2)
8 ) ;

Table created.

bspdb>
bspdb>
bspdb>insert into foo values ('I1',to_date('200605','yyyymm'),10,20,30,40);

1 row created.

bspdb>insert into foo values ('I1',to_date('200604','yyyymm'),10,20,30,40);

1 row created.

bspdb>insert into foo values ('I1',to_date('200603','yyyymm'),10,20,31,40);

1 row created.

bspdb>insert into foo values ('I1',to_date('200602','yyyymm'),10,20,30,40);

1 row created.

bspdb>insert into foo values ('I1',to_date('200601','yyyymm'),10,20,30,40);

1 row created.

bspdb>commit;

Commit complete.

bspdb>
bspdb>
bspdb>select item ,
2 max(eff_frm) keep (dense_rank last order by
3 (item||flag || col1||col2||col3||col4)) eff_frm ,
4 col1,col2,col3,col4,
5 to_char(max(mth),'yyyymm') end_irm
6 from (
7 select item,mth
8 ,col1,col2,col3,col4 ,eff_frm,flag
9 from foo
10 model return all rows
11 partition by (item )
12 dimension by (row_number() over (partition by item order by mth desc) as rn)
13 measures (mth, col1,col2,col3,col4, mth as eff_frm ,0 as flag )
14 ignore nav
15 rules sequential order (
16 eff_frm[any] order by mth desc =
17 case
18 when col1[cv()]=col1[cv()-1]
19 and col2[cv()]=col2[cv()-1]
20 and col3[cv()]=col3[cv()-1]
21 and col4[cv()]=col4[cv()-1]
22 then eff_frm[cv()-1]
23 else mth[cv()] end ,
24 flag[any] order by mth desc =
25 case
26 when col1[cv()]=col1[cv()-1]
27 and col2[cv()]=col2[cv()-1]
28 and col3[cv()]=col3[cv()-1]
29 and col4[cv()]=col4[cv()-1]
30 then flag[cv()-1]
31 else flag[cv()-1]+1 end
32 )
33 ) group by item,flag , col1,col2,col3,col4 ;

IT EFF_FRM COL1 COL2 COL3 COL4 END_IRM
-- --------- ---------- ---------- ---------- ---------- -------
I1 01-MAY-06 10 20 30 40 200605
I1 01-MAR-06 10 20 31 40 200603
I1 01-FEB-06 10 20 30 40 200602

bspdb>
bspdb>
bspdb>
bspdb>


============


Srini
Re: sql query to compare records [message #209604 is a reply to message #209408] Fri, 15 December 2006 17:00 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


Please try this query because
dense_rank ..last construct gives incorretc result on concatenated order.

And this one is simpler too.


=====
select item ,
to_char(min(mth),'yyyymm') eff_frm ,
col1,col2,col3,col4,
to_char(max(mth),'yyyymm') end_irm
from (
select item,mth
,col1,col2,col3,col4 ,flag ,rn
from foo
model return all rows
partition by (item )
dimension by (row_number() over (partition by item order by mth desc) as rn)
measures (mth, col1,col2,col3,col4, 0 as flag )
ignore nav
rules sequential order (
flag[any] order by mth desc =
case
when col1[cv()]=col1[cv()-1]
and col2[cv()]=col2[cv()-1]
and col3[cv()]=col3[cv()-1]
and col4[cv()]=col4[cv()-1]
then flag[cv()-1]
else flag[cv()-1]+1 end
)
) group by item,flag , col1,col2,col3,col4
/


Srini
Re: sql query to compare records [message #210088 is a reply to message #209604] Tue, 19 December 2006 06:01 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
it will be good if the coding is posted attached with a
..it makes the code more readable.please read the sticky before posting
Re: sql query to compare records [message #210110 is a reply to message #210088] Tue, 19 December 2006 07:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you add [notag]-tags around your [code][/code]-tags, they are readable as tags instead of interpreted
comparing records and merging them into one record. [message #213401 is a reply to message #209408] Wed, 10 January 2007 11:14 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

ITEM    MTH       COL1     COL2        COL3         COL4

I1    200605       10       20          30           40
I1    200604       10       20          30           40
I1    200603       10       20          31           40
I1    200602       10       20          30           40
I1    200601       10       20          30           40




The above row needs to be converted like this..



ITEM   EFF_FROM      COL1    COL2    COL3    COL4  END_IRM
I1     200604         10      20      30      40   200605
I1     200603         10      20      31      40   200603
I1     200601         10      20      30      40   200602




basically i want to compare the consecutive rows to see if there is a change in value in columns col1 to col4.the idea is to combine the records for which all 4 columns are same into a eff_from and eff_to.If this can be easily done in a pl/sql, please let me know how to proceed. Could this be done in pl/sql?


Thanks
Re: comparing records and merging them into one record. [message #213402 is a reply to message #213401] Wed, 10 January 2007 11:21 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
There is NO such concept as consecutive records in any RDBMS.
Any database engine is free to return records in an indeterminate order; unless an ORDER BY clause is used.

[Updated on: Wed, 10 January 2007 11:26] by Moderator

Report message to a moderator

Re: comparing records and merging them into one record. [message #213464 is a reply to message #213401] Wed, 10 January 2007 19:40 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This is the same question posted last month:

http://www.orafaq.com/forum/m/209408/85219/?srch=eff_from#msg_209408

Would help to continue with original thread so others can see what has already been proposed.
Previous Topic: create table statement
Next Topic: Identifying Similar Set of Rows in a table
Goto Forum:
  


Current Time: Sat Dec 03 08:22:53 CST 2016

Total time taken to generate the page: 0.10753 seconds