Hi Experts,
Please find below table & data.
create table orders ( order_id varchar2(10),quantity varchar2(10), price varchar2(10), id varchar2(10));
insert into orders values ('O1','2','100','id1');
insert into orders values ('O1','3','150','id2');
insert into orders values ('O1','1','50','id3');
insert into orders values ('O2','4','160','id4');
insert into orders values ('O2','4','120','id5');
insert into orders values ('O2','2','80','id6');
insert into orders values ('O3','2','60','id7');
insert into orders values ('O3','3','90','id8');
insert into orders values ('O3','3','85','id9');
insert into orders values ('O3','4','120','id10');
insert into orders values ('O3','4','110','id11');In the above table, for a given order_id whenever quantity value changes corresponding price value also changes, however for few records the price value changes even when there is no change in quantity for a given order_id.
From the above example, incorrect records are -
order id quantity price id
O2 4 120 id5
O3 3 85 id9
O3 4 110 id11
Could you please help me in forming a query which will identify such incorrect data.
Thanks in advance.
What exactly is the basis for knowing a price has changed without a corresponding value change? If, when you sort by order_id and id, the quantity for the previous row matches (for the same order_id), but not the price?
If so, you can use lag to find the prior values. Then compare them to the current with something like this:
with prev_qty_and_price as (
select o.*,
lag(quantity) over (partition by order_id order by substr(id, 3)) prev_qty,
lag(price) over (partition by order_id order by substr(id, 3)) prev_price
from orders o
)
select * from prev_qty_and_price
where quantity = prev_qty
and price <> prev_price;
ORDER_ID QUANTITY PRICE ID PREV_QTY PREV_PRICE
---------- ---------- ---------- ---------- ---------- ----------
O2 4 120 id5 4 160
O3 4 110 id11 4 120
O3 3 85 id9 3 90