Complicated Calculation Using Oracle SQL

Question

I have created a database for an imaginary solicitors, my last query to complete is driving me insane. I need to work out the total a solicitor has made in their career with the company, I have time_spent and rate to multiply and special rate to add. (special rate is a one off charge for corporate contracts so not many cases have them). the best I could come up with is the code below. It does what I want but only displays the solicitors working on a case with a special rate applied to it.

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

I have ordered the table to show the highest amount first so i can use ROWNUM to only show the top 10% earners.

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

I'm suspicious of your use of ROWNUM in your example query...

Oracle9i+ supports analytic functions, like ROW_NUMBER and NTILE, to make queries like your example easier. Analytics are also ANSI, so the syntax is consistent when implemented (IE: Not on MySQL or SQLite). I re-wrote your query as:

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

If you're new to SQL, I recommend using ANSI-92 syntax. Your example uses ANSI-89, which doesn't support OUTER JOINs and is considered deprecated. I used a LEFT OUTER JOIN against the SPECIAL_RATE table because not all jobs are likely to have a special rate attached to them.

It's also not recommended to include an ORDER BY in views, because views encapsulate the query -- no one will know what the default ordering is, and will likely include their own (waste of resources potentially).