Databases Reference
In-Depth Information
ORDER_ID CUSTOMER_ID ORDER_TOTAL
---------- ----------- -----------
2361 108 122131.3
1 row selected.
Before calling back the customer, Sharon finds out from the Accounting
department that a day ago, two of the orders were incorrectly modified with an
additional surcharge. To confirm whether this particular order was affected by
the accounting error, she uses a flashback query to see if this order had a different
order total two days ago:
select order_id, customer_id, order_total from orders
as of timestamp (sysdate - 2)
where order_id = 2361;
ORDER_ID CUSTOMER_ID ORDER_TOTAL
---------- ----------- -----------
2361 108 120131.3
1 row selected.
This flashback query confirms that the order total for this order was $2,000 less
two days ago. The AS OF TIMESTAMP clause specifies how far back in the past you
want to view the contents of this table. In this case, (sysdate - 2) evaluates to
today's date minus two days—in other words, two days ago. Sharon concludes
that at some point in the past two days, this was one of the orders that were incor-
rectly modified. To find all of the orders that have the incorrect surcharge, she uses
another flashback query as a nested query to compare the order totals:
select o.order_id, o.customer_id,
o.order_total "CURR_TOTAL", oo.order_total "ORIG_TOTAL"
from orders o,
(select order_id, order_total from orders
as of timestamp (sysdate - 2)) oo
where o.order_id = oo.order_id and
o.order_total != oo.order_total;
ORDER_ID CUSTOMER_ID ORDER_TOTAL ORIG_TOTAL
---------- ----------- ----------- ----------
2361 108 122131.3 120131.3
2367 148 146054.8 144054.8
2 rows selected.
