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
86 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
  • 9
  • 7
  • 2
  • +1
19 Comments
 

Author Comment

by:priyanka velusamy
Comment Utility
i am using oracle 11g
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Changed Topic to Oracle Database
0
 

Author Comment

by:priyanka velusamy
Comment Utility
OK THANK YOU
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
i will try your  code @slightwv (䄆 Netminder).THANKS
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
Comment Utility
HI slightwv ,Thanks for squery, the  execution is  taking lot of time but not giving output.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> 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
Comment Utility
ok .Thank you
0
 

Author Comment

by:priyanka velusamy
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now