CODEWITHSUNDEEP
sqljoinoracle11gsubquery
monitorjbl
monitorjbl

Reputation: 4350

Oracle join on first row of a subquery

This may seem simple, but somehow it isn't. I have a table of historical rate data 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 hours that need to be joined to a single row of TBL_A in order to get costing info:

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

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:

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

EDIT: The desired output would be this:

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

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.

Upvotes: 4

Views: 7159

Answers (2)

Ren&#233; Nyffenegger
Ren&#233; Nyffenegger

Reputation: 40603

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
;

Upvotes: 1

Eric
Eric

Reputation: 95203

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 why this is happening: Only correlated subqueries are aware of the context in which they're being run, since they're run for each row. A joined subquery is actually executed prior to the join, and so it has no knowledge of the surrounding tables. You need to return all identifying information with it to make the join in the top level of the query, rather than trying to do it within the subquery.

Upvotes: 4

Related Questions