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
108 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
ID: 41869596
i am using oracle 11g
0
 
LVL 28

Expert Comment

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

Author Comment

by:priyanka velusamy
ID: 41869739
OK THANK YOU
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 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 76

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 76

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 76

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 76

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 76

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 76

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 76

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
null value 15 101
Oracle Next Available Number 2 41
Can anyone please tell me what does below Stored Procedure does? 4 29
constraint check 2 40
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

776 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