0

i got some problems on my approach to join a couple of tables by a date range. This are my tables:

RETURNS:

RETURN_DATE     RETURN_AMOUNT
02.05.14        2
02.05.14        3
02.05.14        1
02.05.14        7
02.05.14        5
02.05.14        2
02.05.14        1

and

ORDERS:

ORDER_DATE      ORDER_AMOUNT
02.05.14        50

As a first step, i created a date range and then tried to join these two tables to it. This is my SELECT statement:

SELECT
  DAT,
  RETURN_AMOUNT,
  ORDER_AMOUNT

  FROM
    (
      SELECT
      TO_DATE('02.05.2014','dd.mm.yyyy')+ROWNUM-1 AS DAT

      FROM 
      all_objects

      WHERE 
      ROWNUM <= (TO_DATE('02.05.2014','dd.mm.yyyy')-TO_DATE('02.05.2014','dd.mm.yyyy')+1)
    ) LEFT JOIN RETURNS ON RETURN_DATE = DAT
    LEFT JOIN ORDERS ON ORDER_DATE = DAT

    ORDER BY DAT;

This was the Result:

RESULT:

DAT         RETURN_AMOUNT   ORDER_AMOUNT
02.05.14    1               50
02.05.14    2               50
02.05.14    5               50
02.05.14    2               50
02.05.14    1               50
02.05.14    3               50
02.05.14    7               50

Thats a problem because when i GROUP BY DAT and then sum up the amounts, i got a wrong ORDER_AMOUNT for this day.

How can i achive, that ORDER_AMOUNT is only resulted once?

Thanks, Stefan.

4
  • For a start, get rid of the hideous date inline view and just use a simple >= and =< in the WHERE clause instead.
    – Philᵀᴹ
    Feb 27, 2015 at 8:17
  • I think that is not possible. I need a row for every day, also when no order was placed and no returns where received. (i.e. weekend).
    – user25186
    Feb 27, 2015 at 8:38
  • I wonder how your query even worked, without an alias for the derived table. And if it did, why on earth Oracle allows such syntax. Feb 27, 2015 at 10:25
  • It's definitely working. I copy/pasted it right out of the query window. I have Oracle 12c.
    – user25186
    Feb 27, 2015 at 10:31

2 Answers 2

1

You could join to the 2 tables separately (and GROUP BY) and then join them together:

WITH dates (dat) AS
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM 
        all_objects
      WHERE 
        ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    ) 
, ret (dat, return_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(r.return_amount), 0) AS return_amount
      FROM dates  d
        LEFT JOIN returns  r ON r.RETURN_DATE = d.dat
      GROUP BY
        d.dat
    )
, ord (dat, order_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(o.order_amount), 0) AS order_amount
      FROM dates  d
        LEFT JOIN orders  o ON o.ORDER_DATE = d.dat
      GROUP BY
        d.dat
    )
SELECT
  r.dat,
  r.return_amount,
  o.order_amount
FROM
  ret  r
    JOIN ord  o ON o.dat = r.dat
ORDER BY 
  r.dat ;

Or use inline subqueries:

SELECT
  d.dat,
  COALESCE( ( SELECT SUM(r.return_amount) 
              FROM returns  r 
              WHERE r.dat = d.dat
            ), 0) 
    AS return_amount,
  COALESCE( ( SELECT SUM(o.order_amount) 
              FROM orders  o 
              WHERE o.dat = d.dat
            ), 0) 
    AS order_amount
FROM
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM 
        all_objects
      WHERE 
        ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    ) d
ORDER BY 
  d.dat ;
1

Thanks for your help. I changed my query like this and it's working finde. 😊

SELECT
  DATES.DAT,
  RET.RETURN_AMOUNT,
  ORD.ORDER_AMOUNT

  FROM
    (
      SELECT
      TO_DATE('02.05.2014','dd.mm.yyyy')+ROWNUM-1 AS DAT

      FROM 
      all_objects

      WHERE 
        ROWNUM <= (TO_DATE('02.05.2014','dd.mm.yyyy')-TO_DATE('02.05.2014','dd.mm.yyyy')+1)
    ) DATES LEFT JOIN 
    (
      SELECT 
        RETURN_DATE,
        SUM(RETURN_AMOUNT) AS RETURN_AMOUNT 

        FROM 
          RETURNS 

        GROUP BY 
          RETURN_DATE
    ) RET ON RET.RETURN_DATE = DAT
    LEFT JOIN 
    (
      SELECT 
        ORDER_DATE,
        SUM(ORDER_AMOUNT) AS ORDER_AMOUNT 

        FROM 
          ORDERS 

        GROUP BY 
          ORDER_DATE
    ) ORD ON ORD.ORDER_DATE = DAT

  ORDER BY DAT;
1
  • Yes, that is similar to my 1st query - and simpler. Feb 27, 2015 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.