Today I was asked to re-process 700+ orders. A list of Order_IDs was provided and I was expected to update the order status and process date. It seems to be an easy task,
update orders
set status = 'PAID',last_modified_dt = sysdate
where order_id in (1234, 1235, 1239)
But the problem is there are some orders missing.
There are no such orders exist in the database for some order_IDs.
I was asked to provide a list of those missing order_IDs.
It is easy to find order_IDs exist in the database,
select order_ID
from orders
where order_id in (1234, 1235, 1239)
For example, only order_ID 1234 and 1235 exist. Order_ID 1239 does not exist in the database. How could we find the non-existing orders?
My first thought is spool the result of the select statement to a file, then compare it with the file including all order_IDs using diff command.
It definitely works. Is there a neat way in SQL to do it?
I found a very handy collection data type SYS.DBMS_DEBUG_VC2COLL, which can convert a list to an array. Here are some examples.
select *
from table(sys.dbms_debug_vc2coll('a','b','c'));
COLUMN_VALUE
------------
a
b
c
select *
from table(sys.dbms_debug_vc2coll(1,2,3));
COLUMN_VALUE
------------
1
2
3
Let us go back to my case. How can I achieve my goal?
select *
from table(sys.dbms_debug_var2coll(1234, 1235, 1239)
minus
select order_ID
from orders
where order_id in (1234, 1235, 1239)
That is it. It solves the problem without using diff command. Is there any other better way?
No comments:
Post a Comment