• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

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

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
priyanka velusamy
Asked:
priyanka velusamy
  • 9
  • 7
  • 2
  • +1
1 Solution
 
priyanka velusamyAuthor Commented:
i am using oracle 11g
0
 
Pawan KumarDatabase ExpertCommented:
Changed Topic to Oracle Database
0
 
priyanka velusamyAuthor Commented:
OK THANK YOU
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Geert GruwezOracle dbaCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
priyanka velusamyAuthor Commented:
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
 
priyanka velusamyAuthor Commented:
i will try your  code @slightwv (䄆 Netminder).THANKS
0
 
Geert GruwezOracle dbaCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
priyanka velusamyAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
priyanka velusamyAuthor Commented:
HI slightwv ,Thanks for squery, the  execution is  taking lot of time but not giving output.
0
 
slightwv (䄆 Netminder) Commented:
>> 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
 
priyanka velusamyAuthor Commented:
ok .Thank you
0
 
priyanka velusamyAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
priyanka velusamyAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now