title: Oracle join on first row of a subquery
body: <p>This may seem simple, but somehow it isn't. I have a table of historical rate data called TBL_A that looks like this:</p>
  
  <pre><code>|   id   |  rate  |  added_date  |
  |--------|--------|--------------|
  |  bill  |  7.50  |   1/24/2011  |
  |  joe   |  8.50  |   5/3/2011   |
  |  ted   |  8.50  |   4/17/2011  |
  |  bill  |  9.00  |   9/29/2011  |
  </code></pre>
  
  <p>In TBL_B, I have hours that need to be joined to a single row of TBL_A in order to get costing info:</p>
  
  <pre><code>|   id   |  hours  |  added_date  |
  |--------|---------|--------------|
  |  bill  |   10    |   2/26/2011  |  
  |  ted   |   4     |   7/4/2011   |
  |  bill  |   9     |   10/14/2011 |
  </code></pre>
  
  <p>As you can see, for Bill there are two rates in TBL_A, but they have different dates. To properly get Bill's cost for a period of time, you have to join each row of TBL_B on an row in TBL_A that is appropriate for the date.</p>
  
  <p>I figured this would be easy; because this didn't have to an exceptionally fast query, I could just do a separate subquery for each row of costing info. However, joined subqueries apparently cannot "see" other tables that they are joined on. This query throws an invalid identifier (ORA-00904) on anything in the subquery that has the "h" alias:</p>
  
  <pre><code>SELECT h.id, r.rate * h.hours as "COST", h.added_date
       FROM TBL_B h
       JOIN (SELECT * FROM (
                  SELECT i.id, i.rate 
                       FROM TBL_A i 
                  WHERE i.id = h.id and i.added_date &lt; h.added_date
                  ORDER BY i.added_date DESC)
             WHERE rownum = 1) r
        ON h.id = r.id
  </code></pre>
  
  <p>If the problem is simply scoping, I don't know if the approach I took can ever work. But all I'm trying to do here is get a single row based on some criteria, so I'm definitely open to other methods.</p>
  
  <p>EDIT: The desired output would be this:</p>
  
  <pre><code>|   id   |   cost  |  added_date  |
  |--------|---------|--------------|
  |  bill  |   75    |   2/26/2011  |  
  |  ted   |   34    |   7/4/2011   |
  |  bill  |   81    |   10/14/2011 |
  </code></pre>
  
  <p>Note that Bill has two different rates in the two entries in the table. The first row is 10 * 7.50 = 75 and the second row is 9 * 9.00 = 81.</p>