Oracle joins first row of subquery

Question

It may seem simple, but for some reason it is not. I have a speed history table called TBL_A that looks like this:

| 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 | 

In TBL_B, I have a clock that needs to be connected to one line of TBL_A in order to get cost information:

 | id | hours | added_date | |--------|---------|--------------| | bill | 10 | 2/26/2011 | | ted | 4 | 7/4/2011 | | bill | 9 | 10/14/2011 | 

As you can see, for Bill there are two bets in TBL_A, but they have different dates. To get the Bill value correctly for a certain period of time, you need to append each line of TBL_B to the line in TBL_A, which is suitable for the date.

I realized that it would be easy; because it wasn’t for an exceptionally quick request, I could just make a separate subquery for each row of cost information. However, joined subqueries apparently cannot "see" the other tables to which they are joined. This query returns an invalid identifier (ORA-00904) for something in the subquery with the alias "h":

 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 < h.added_date ORDER BY i.added_date DESC) WHERE rownum = 1) r ON h.id = r.id 

If the problem is simple, I don’t know if the approach I made can ever work. But all I am trying to do here is to get one row based on some criteria, so I am definitely open to other methods.

EDIT: The desired result will be as follows:

 | id | cost | added_date | |--------|---------|--------------| | bill | 75 | 2/26/2011 | | ted | 34 | 7/4/2011 | | bill | 81 | 10/14/2011 | 

Please note that Bill has two different bets in two entries in the table. The first line is 10 * 7.50 = 75, and the second line is 9 * 9.00 = 81.

Answer 1

Try using not exists :

 select b.id, a.rate, b.hours, a.rate*b.hours as "COST", b.added_date, a.added_date from tbl_b b inner join tbl_a a on b.id = a.id where a.added_date < b.added_date and not exists ( select 1 from tbl_a a2 where a2.added_date > a.added_date and a2.added_date < b.added_date and a2.id = a.id ) 

As an explanation of why this happens: only correlated subqueries are aware of the context in which they are launched, as they are executed for each row. The joined subquery is actually executed before the join, and therefore it does not know the surrounding tables. You need to return all the identifying information in order to make the connection at the top level of the request, and not try to do it in a subquery.

Answer 2

 select id, cost, added_date from ( select h.id, r.rate * h.hours as "COST", h.added_date, -- For each record, assign r=1 for 'newest' rate row_number() over (partition by h.id, h.added_date order by r.added_date desc) r from tbl_b h, tbl_a r where r.id = h.id and -- Date of rate must be entered before -- hours billed: r.added_date < h.added_date ) where r = 1 ;