Selecting default row if no rows return with join condition values from other tables

I have two tables ORDERS and RATE:

CREATE TABLE ORDERS
(   ID NUMBER(12,0), 
    DOW NUMBER(12,0), 
    COUNT NUMBER(12,0)
);

CREATE TABLE RATE
(   ID NUMBER(12,0), 
    DOW NUMBER(12,0), 
    RATE NUMBER(12,0)
);
INSERT INTO ORDERS (ID, DOW, COUNT) VALUES ('1', '1', '5');
INSERT INTO ORDERS (ID, DOW, COUNT) VALUES ('1', '3', '7');
INSERT INTO ORDERS (ID, DOW, COUNT) VALUES ('2', '1', '2');
INSERT INTO RATE (ID, DOW, RATE) VALUES ('1', '1', '10');
INSERT INTO RATE (ID, DOW, RATE) VALUES ('1', '2', '20');
INSERT INTO RATE (ID, DOW, RATE) VALUES ('1', '0', '50');

and I would like to get the costs of all the orders in table ORDERS, given that a default rate of 10 is used for those orders which do not have rates in the RATE table for the corresponding ids, and if an id is available in the RATE table, but no rate for a certain dow of that id is available, a default rate with dow 0 is always available for that id.

For the above example, the following would be expected:

id  dow   cost
--------------
1    1     50
1    3     350
2    1     20

Note that the 350 for id=1 and dow = 3 comes from count 7 multiplied by rate 50 for id=1 and dow=0, as there is no rate for id=1 and dow=3 in the table. I have tried the following with no luck:

SELECT o.id, o.dow, o.count*(coalesce(r.rate, 10)) as cost
FROM orders o
left join rate r
on r.id = o.id
and  r.dow = o.dow
;

and the following has compilation error:

ORA-00904: “O”.”DOW”: invalid identifier

904. 00000 – “%s: invalid identifier”

SELECT o.id ,o.dow ,o.count * (coalesce(r.rate, 10)) cost
FROM orders o
LEFT JOIN (
    SELECT id, dow, rate
    FROM rate
    WHERE id = o.id
        AND dow = o.dow
    
    UNION ALL
    
    SELECT id ,dow ,rate
    FROM rate
    WHERE id = o.id
        AND dow = 0
        AND NOT EXISTS (
            SELECT 1
            FROM rate
            WHERE id = o.id
                AND dow = o.dow
            )
    ) r
    ON r.id = o.id
;

Can someone please help with the query?

Answer

You can use multiple outer joins with RATE table to fetch the desired result as following:

SQL> SELECT
  2      O.ID,
  3      O.DOW,
  4      O.COUNT * COALESCE(R.RATE, RD.RATE) AS COST
  5  FROM
  6      ORDERS O
  7      LEFT JOIN RATE R ON ( O.DOW = R.DOW )
  8      LEFT JOIN RATE RD ON ( R.ID IS NULL
  9                             AND RD.DOW = 0 )
 10  ORDER BY O.ID;

        ID        DOW       COST
---------- ---------- ----------
         1          1         50
         1          3        350
         2          1         20

SQL>

Cheers!!