ORDER BY in MERGE statement USING clause
Posted on 2014-09-29
Populate into relational (physical)table via nested table (collection).
The collection has a field ITR_ORDER (iteration order) which is basically a sequence so need to read this collection in order and insert (if not matched) into table in order.
Somehow in the MERGE statement USING clause the ORDER BY is not working (compiles, but optimizer seems to ignore ORDER BY clause) as expected.., how to enforce it??
Alternative is looping and updated 0 rows then insert... Don''t want to use this approach.... want to do it via MERGE...
MERGE INTO vendors dest
USING (SELECT tt.*
FROM TABLE(CAST(v_vendors_tbl AS vendors_tbl)) tt
ORDER BY ITR_ORDER) src
ON (dest.vendors_id = src.vendors_id)
WHEN MATCHED THEN
WHEN NOT MATCHED THEN