Complex calculation using Oracle SQL

Question

I created a database for imaginary lawyers, my last request to complete is driving me crazy. I need to understand what made the solicitor in his career in the company, I have time_spent, and rateto multiply and special rates for uploads. (a special rate is a one-time fee for corporate contracts, so there are not many of them). the best I could come up with is the code below. He does what I want, but displays only solicitors working in case with a special speed applied to him.

I essentially want it to display the query result in a table, even if the special speed is NULL.

I asked the table to show the highest amount, so I can use ROWNUM to show only the top 10%.

CREATE VIEW rich_solicitors AS 
  SELECT notes.time_spent * rate.rate_amnt + special_rate.s_rate_amnt AS solicitor_made,          
        notes.case_id
   FROM notes, 
        rate, 
        solicitor_rate, 
        solicitor, 
        case, 
        contract, 
        special_rate
  WHERE notes.solicitor_id = solicitor.solicitor_id
    AND solicitor.solicitor_id = solicitor_rate.solicitor_id
    AND solicitor_rate.rate_id = rate.rate_id
    AND notes.case_id = case.case_id
    AND case.contract_id = contract.contract_id
    AND contract.contract_id = special_rate.contract_id
ORDER BY -solicitor_made;

Query:

SELECT * 
  FROM rich_solicitors
 WHERE ROWNUM <= (SELECT COUNT(*)/10 
                    FROM rich_solicitors)

Answer 1

.

, :

AND contract.contract_id = special_rate.contract_id (+) 

special_rate. * , :

+ special_rate.s_rate_amnt

:

+ coalesce(special_rate.s_rate_amnt,0)

Answer 2

ROWNUM ...

Oracle9i + , ROW_NUMBER NTILE, , . ANSI, (IE: MySQL SQLite). :

SELECT x.*
  FROM (SELECT n.time_spent * r.rate_amnt + COALESCE(spr.s_rate_amnt, 0) AS solicitor_made,          
               n.case_id,
               NTILE(10) OVER (ORDER BY solicitor_made) AS rank
          FROM NOTES n 
          JOIN SOLICITOR s ON s.solicitor_id = n.solicitor_id
          JOIN SOLICITOR_RATE sr ON sr.solicitor_id = s.solicitor_id 
          JOIN RATE r ON r.rate_id = sr.rate_id
          JOIN CASE c ON c.case_id = n.case_id 
          JOIN CONTRACT cntrct ON cntrct.contract_id = c.contract_id
     LEFT JOIN SPECIAL_RATE spr ON spr.contract_id = cntrct.contract_id) x
 WHERE x.rank = 1

SQL, ANSI-92. ANSI-89, OUTER JOINs . LEFT OUTER JOIN SPECIAL_RATE, , , , .

ORDER BY , - , , , , ( ).