create table TRY_date_detail
  (
  item_date date,
  item_order number
  );

create table TRY_date_master
  (
  master_date date,
  date_amount number
  );

insert all
  INTO TRY_date_detail values (to_date('01-01-2011','DD-MM-YYYY'), 1)
  INTO TRY_date_detail values (to_date('01-01-2011','DD-MM-YYYY'), 2)
  INTO TRY_date_detail values (to_date('01-01-2011','DD-MM-YYYY'), 3)
  INTO TRY_date_detail values (to_date('01-01-2011','DD-MM-YYYY'), 4)
  INTO TRY_date_detail values (to_date('01-01-2011','DD-MM-YYYY'), 5)
    INTO TRY_date_master values (to_date('01-01-2011','DD-MM-YYYY'), 6432)
    INTO TRY_date_master values (to_date('01-01-2011','DD-MM-YYYY'), 1111)
  INTO TRY_date_detail values (to_date('11-08-2012','DD-MM-YYYY'), 11)
  INTO TRY_date_detail values (to_date('11-08-2012','DD-MM-YYYY'), 12)
  INTO TRY_date_detail values (to_date('11-08-2012','DD-MM-YYYY'), 13)
  INTO TRY_date_detail values (to_date('11-08-2012','DD-MM-YYYY'), 14)
    INTO TRY_date_master values (to_date('11-08-2012','DD-MM-YYYY'), 8913)
  INTO TRY_date_detail values (to_date('09-03-2014','DD-MM-YYYY'), 21)
  INTO TRY_date_detail values (to_date('09-03-2014','DD-MM-YYYY'), 22)
  INTO TRY_date_detail values (to_date('09-03-2014','DD-MM-YYYY'), 23)
    INTO TRY_date_master values (to_date('09-03-2014','DD-MM-YYYY'), 1234)
  INTO TRY_date_detail values (to_date('11-03-2014','DD-MM-YYYY'), 33)
select * from dual;
 

-- My query is:
SELECT ITEM_DATE, ITEM_ORDER, NULL DATE_AMOUNT
  FROM TRY_date_detail
  WHERE ITEM_DATE IN (SELECT DISTINCT master_date FROM TRY_date_master)
UNION
SELECT MASTER_DATE, NULL, SUM(DATE_AMOUNT)
  FROM TRY_DATE_MASTER
  GROUP BY MASTER_DATE;

 

--My query result:
ITEM_DATE           ITEM_ORDER    DATE_AMOUNT
01-Jan-11 12:00:00 AM    1    (null)
01-Jan-11 12:00:00 AM    2    (null)
01-Jan-11 12:00:00 AM    3    (null)
01-Jan-11 12:00:00 AM    4    (null)
01-Jan-11 12:00:00 AM    5    (null)
01-Jan-11 12:00:00 AM    (null)    7543
11-Aug-12 12:00:00 AM    11    (null)
11-Aug-12 12:00:00 AM    12    (null)
11-Aug-12 12:00:00 AM    13    (null)
11-Aug-12 12:00:00 AM    14    (null)
11-Aug-12 12:00:00 AM    (null)    8913
09-Mar-14 12:00:00 AM    21    (null)
09-Mar-14 12:00:00 AM    22    (null)
09-Mar-14 12:00:00 AM    23    (null)
09-Mar-14 12:00:00 AM    (null)    1234

 

-- What I need is to display the Date_amount with the last line in each date as follows:

ITEM_DATE            ITEM_ORDER    DATE_AMOUNT
01-Jan-11 12:00:00 AM    1    (null)
01-Jan-11 12:00:00 AM    2    (null)
01-Jan-11 12:00:00 AM    3    (null)
01-Jan-11 12:00:00 AM    4    (null)
01-Jan-11 12:00:00 AM    5    7543   
11-Aug-12 12:00:00 AM    11    (null)
11-Aug-12 12:00:00 AM    12    (null)
11-Aug-12 12:00:00 AM    13    (null)
11-Aug-12 12:00:00 AM    14    8913   
09-Mar-14 12:00:00 AM    21    (null)
09-Mar-14 12:00:00 AM    22    (null)
09-Mar-14 12:00:00 AM    23    1234

SQL> select item_date, item_order,
  2         case when item_order = max(item_order) over (partition by item_date) then
  3            (select sum(date_amount) from TRY_date_master m where m.master_date=d.item_date)
  4         end date_amount
  5  from TRY_date_detail d
  6  order by item_date, item_order
  7  /
ITEM_DATE           ITEM_ORDER DATE_AMOUNT
------------------- ---------- -----------
01/01/2011 00:00:00          1
01/01/2011 00:00:00          2
01/01/2011 00:00:00          3
01/01/2011 00:00:00          4
01/01/2011 00:00:00          5        7543
11/08/2012 00:00:00         11
11/08/2012 00:00:00         12
11/08/2012 00:00:00         13
11/08/2012 00:00:00         14        8913
09/03/2014 00:00:00         21
09/03/2014 00:00:00         22
09/03/2014 00:00:00         23        1234
11/03/2014 00:00:00         33

13 rows selected.

SQL> WITH t1 AS
  2    (SELECT MASTER_DATE,
  3      SUM(DATE_AMOUNT) amt
  4    FROM TRY_DATE_MASTER
  5    GROUP BY MASTER_DATE
  6    ),
  7    t2 AS
  8    (SELECT a.*,
  9      row_number() over(partition BY item_date order by item_order DESC) rn
 10    FROM TRY_date_detail A
 11    ORDER BY item_order
 12    )
 13  SELECT t2.item_date,
 14    t2.item_order,
 15    DECODE(t2.rn, 1, t1.amt, NULL)
 16  FROM t1,
 17    t2
 18  WHERE t1.master_date=t2.item_date
 19  /

ITEM_DATE ITEM_ORDER DECODE(T2.RN,1,T1.AMT,NULL)
--------- ---------- ---------------------------
01-JAN-11          1
01-JAN-11          2
01-JAN-11          3
01-JAN-11          4
01-JAN-11          5                        7543
11-AUG-12         11
11-AUG-12         12
11-AUG-12         13
11-AUG-12         14                        8913
09-MAR-14         21
09-MAR-14         22
09-MAR-14         23                        1234

12 rows selected.

SQL>