0

In MERGE PROCEDURE, how can we use order by while inserting? I want to insert those records which are ORDER BY CREATED_DATETIME from the SRC table. 

create or replace PROCEDURE FULFILLABLE_ORDERS 
AS 
BEGIN 
   MERGE INTO RPT_SCI.ORDER_FULFILLABLE TRGT 
   USING (
      select * 
      from RPT_SCI.VI_ORDER_FULFILLABLE 
      order by CREATED_DTTM DESC
   ) SRC ON ( TRGT.TC_ORDER_ID = SRC.TC_ORDER_ID) 
   WHEN MATCHED THEN UPDATE (.............) 
   WHEN NOT MATCHED THEN INSERT (..............);
END;

My database is Oracle.

Sander de Jong
  • 351
  • 6
  • 18
Agnostic
  • 39
  • 5
  • Please show what you have tried first. – Stanley Mungai Jan 08 '16 at 14:10
  • create or replace PROCEDURE FULFILLABLE_ORDERS AS BEGIN MERGE INTO RPT_SCI.ORDER_FULFILLABLE TRGT USING (select * from RPT_SCI.VI_ORDER_FULFILLABLE order by CREATED_DTTM DESC) SRC ON ( TRGT.TC_ORDER_ID = SRC.TC_ORDER_ID) WHEN MATCHED THEN UPDATE (.............) WHEN NOT MATCHED THEN INSERT (..............) Basically I want to loop the SRC table order by create_datetime – Agnostic Jan 08 '16 at 14:17
  • Basically I want to loop the SRC table order by create_datetime, so that it insert or update as per the loop. I need to insert / update records like FIFO – Agnostic Jan 08 '16 at 14:23
  • 1
    What's the reason for this requirement? There is no way to order how Oracle merges the rows into the target table without splitting the merge statement into separate insert and update sql statements. – Boneist Jan 08 '16 at 14:39
  • @Boneist - are you sure ? – Agnostic Jan 08 '16 at 14:48
  • Pretty sure. Feel free to peruse [the documentation](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606) yourself, though! *{:-) I'm still curious as to the reason why you need to do this, though. Ordinary heap tables in Oracle don't have any implicit ordering to how the data is stored, so I'm struggling to understand why you would care in what order the data is inserted. – Boneist Jan 08 '16 at 14:52
  • Requirement is FIFO, the order which come first have get items first. I have changed the ON CLAUSE it did order by order id but when i add datetime its doesnt , dont know why.. – Agnostic Jan 08 '16 at 14:57
  • Inserting in a specific order doesn't make sense. Rows in a relational table are ***not*** sorted. The insertion order has absolutely no relevance for the retrieval order. The ***only*** (really: the **only**) way to get a specific sort order when _retrieving_ rows is to use an `order by`. –  Jan 08 '16 at 15:07
  • order by on SRC ?!?.... I already tried as shown in my above example. Doesnt work – Agnostic Jan 08 '16 at 15:10
  • There is similar one in SQL SERVER in below link. http://stackoverflow.com/questions/9566824/sql-server-merge-statement-and-order-by-clause – Agnostic Jan 08 '16 at 16:09
  • The requirement might be FIFO, but that only really makes sense if you're talking about queues or stacks. We're not, we're talking about tables, and it really doesn't make sense to do that on an ordinary heap table. What's the reasoning behind the requirement? Why do they want FIFO? What do they think that means in this context? And, for that matter, what do you mean by "first out" when talking about adding data to a table? When you select from a table, you would just add the relevant order by clause into the sql. – Boneist Jan 08 '16 at 17:24

0 Answers0