Solved

sql query to compare two or three rows of same id with different version of same table and find the modified column name

Posted on 2016-11-01
19
134 Views
Last Modified: 2016-11-15
example:

id       version        quantity       datemodified      securityvalue       price ..........................
45        1                  690                28/oct/2016         8                          100
45         2                 690                 28/oct/2016          5                          100
45          3                445                   28/oct/2016        5                             100
45           4              445                    28/oct/2016        5                              400


like this so many columns change(50 columns are there).
i have to identify in same date modified column names

output
version2-securityvaluechanged
version3-quantitychanged
version4-price changed
0
Comment
Question by:priyanka velusamy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 2
  • +1
19 Comments
 

Author Comment

by:priyanka velusamy
ID: 41869596
i am using oracle 11g
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41869628
Changed Topic to Oracle Database
0
 

Author Comment

by:priyanka velusamy
ID: 41869739
OK THANK YOU
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41870099
you are somewhat cryptic

to find the difference from now with 1 hour ago
select * from yourtable
minus 
select * from yourtable as of timestamp 'sysdate-1/24'

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41870182
Try the code below.

I think it will work across dates but you didn't add any examples in your test data and I didn't want to guess.

You also didn't provide expected output if two or more values changed.

I create each change as a separate column.  If you want them in all one columns, it is a simple string concatenation.

select id, version, 
	case when version>1 and quantity!=prev_quantity then 'qty changed' end qty_check,
	case when version>1 and securityvalue!=prev_val then 'val changed' end val_check,
	case when version>1 and price!=prev_price then 'price changed' end price_check
from (
	select id, version, quantity, securityvalue, price ,
		lag(quantity) over(partition by id,datemodified order by version asc) prev_quantity,
		lag(securityvalue) over(partition by id,datemodified order by version asc) prev_val,
		lag(price) over(partition by id,datemodified order by version asc) prev_price 
	from tab1
)
/

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41870191
Quick concatenated version:
select id, version, 
	rtrim(case when version>1 and quantity!=prev_quantity then 'qty-' end ||
	case when version>1 and securityvalue!=prev_val then 'val-' end ||
	case when version>1 and price!=prev_price then 'price-' end,'-') changes
from (
	select id, version, quantity, securityvalue, price ,
		lag(quantity) over(partition by id,datemodified order by version asc) prev_quantity,
		lag(securityvalue) over(partition by id,datemodified order by version asc) prev_val,
		lag(price) over(partition by id,datemodified order by version asc) prev_price 
	from tab1
)
/

Open in new window

0
 

Author Comment

by:priyanka velusamy
ID: 41870217
I have to compare like this
select * from mytable where version=1
minus
select * from mytable where version=2
then i get security value:modified,
but should i do it for all versions ,version number sometimes reach 10.
0
 

Author Comment

by:priyanka velusamy
ID: 41870223
i will try your  code @slightwv (䄆 Netminder).THANKS
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41870279
to find the last changes

with 
  id1 as (
    select id, max(version) last_version from mytable group by id),
  id2 as (
    select id, max(version) pre_last_version from mytable m, id1 
    where m.id = id1.id
      and m.version < id1.last_version)
select m.id, m.quantity, m.securityvalue, m.price 
from mytable m, id1
where m.id = id1.id and m.version = id1.last_version
minus
select m.id, m.quantity, m.securityvalue, m.price 
from mytable m, id2
where m.id = id2.id and m.version = id2.pre_last_version

Open in new window


i'm guessing you'll find almost all rows as what other reason is there to log a record but to change it
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41870321
>>to find the last changes

You are missing a group by and have an ambiguous column in there.

Also, if jut finding the last change was a requirement, there has to be an easier way to do it without having to query the same table sooooo many times.
0
 

Author Comment

by:priyanka velusamy
ID: 41876621
select deal.id,deal.version,
    case when deal.version>0 and flow.quantity!=prev_quantity then 'qty-' end
   
changes
from (
    select deal.id, deal.version, flow.quantity,
        lag(flow.quantity) over(partition by deal.id,deal.datemodified order by deal.version asc) prev_quantity
      From flowtable flow,dealtable deal
    WHERE deal.id=flow.id
)


i checked this code slightwv but i am getting ---------invalid identifier error.
this is data fetched from two tables that is joined by join (id).(dea and flow table)
above i have illustrated:
quantit ,price is in flow table where id,version,datemodified  will be in deal table .
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41877030
You cannot reference table names in the outside query.

I don't have your tables so cannot test this but try this:
select id,version, 
     case when version>0 and quantity!=prev_quantity then 'qty-' end changes
 from (
     select deal.id, deal.version, flow.quantity, 
         lag(flow.quantity) over(partition by deal.id,deal.datemodified order by deal.version asc) prev_quantity
       From flowtable flow,dealtable deal
     WHERE deal.id=flow.id
 )

Open in new window



Here is the complete test case I used to test the code before I posted it:
drop table tab1 purge;
create table tab1(id number, version number, quantity number, datemodified date, securityvalue number, price number);

insert into tab1 values(45,1,690,to_date('28/oct/2016','dd/mon/yyyy'),8,100);
insert into tab1 values(45,2,680,to_date('28/oct/2016','dd/mon/yyyy'),5,100);
insert into tab1 values(45,3,445,to_date('28/oct/2016','dd/mon/yyyy'),5,100);
insert into tab1 values(45,4,445,to_date('28/oct/2016','dd/mon/yyyy'),5,400);
commit;

select id, version, 
	case when version>1 and quantity!=prev_quantity then 'qty changed' end qty_check,
	case when version>1 and securityvalue!=prev_val then 'val changed' end val_check,
	case when version>1 and price!=prev_price then 'price changed' end price_check
from (
	select id, version, quantity, securityvalue, price ,
		lag(quantity) over(partition by id,datemodified order by version asc) prev_quantity,
		lag(securityvalue) over(partition by id,datemodified order by version asc) prev_val,
		lag(price) over(partition by id,datemodified order by version asc) prev_price 
	from tab1
)
/

select id, version, 
	rtrim(case when version>1 and quantity!=prev_quantity then 'qty-' end ||
	case when version>1 and securityvalue!=prev_val then 'val-' end ||
	case when version>1 and price!=prev_price then 'price-' end,'-') changes
from (
	select id, version, quantity, securityvalue, price ,
		lag(quantity) over(partition by id,datemodified order by version asc) prev_quantity,
		lag(securityvalue) over(partition by id,datemodified order by version asc) prev_val,
		lag(price) over(partition by id,datemodified order by version asc) prev_price 
	from tab1
)
/



select id,version, 
     case when version>0 and quantity!=prev_quantity then 'qty-' end
 changes
 from (
     select id, version, quantity, 
         lag(quantity) over(partition by id,datemodified order by version asc) prev_quantity
       From tab1
 )
/

Open in new window

1
 

Author Comment

by:priyanka velusamy
ID: 41882075
HI slightwv ,Thanks for squery, the  execution is  taking lot of time but not giving output.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41882085
>> the  execution is  taking lot of time but not giving output.

LAG tends to out-perform other methods of trying to accomplish the same thing but a lot depends on how big the table is, and all the other general performance related issues.
1
 

Author Comment

by:priyanka velusamy
ID: 41882086
ok .Thank you
0
 

Author Comment

by:priyanka velusamy
ID: 41883688
thank you @ slightwv.i checked for my deals it is working .i have 1 more column in table like
status
N
N
V
N
USER needs the change when the status changes from 'v' to 'N' only and NOT ANY OTHER change in status like 'N' TO 'N'.
can u pls help me in this .
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41884336
Seems like a simple update statement:
update some_table set status='N' where status='V';

If it is more complicated than that, I would open a new question and provide some sample data and expected results.
0
 

Author Comment

by:priyanka velusamy
ID: 41884422
user asked me to provide the changes in qte,price,cash.... as a report only when the status change from 'V' to'N' AND not from any other thing.i cannot update anything in table .only fetching i can do it .
i assumed code can be  like something
status in ('v',....) along with that query
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41884925
Since this is a new requirement and really isn't part of the original question, please ask a new related question.

Again, please provide sample data end expected results.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question