CODEWITHSUNDEEP
oracle-databaseoracle11gsubqueryouter-join
Michael
Michael

Reputation: 507

Outer Join with Sub-query (Oracle 11g)

I need an outer join to a table that is limited by a sub-query. However, Oracle is returning 

ORA-01799: a column may not be outer-joined to a sub-query

I then tried pushing the outer join inside another sub-query but as the original sub-query depends on a field from the main table it is being joined to, this fails with

ORA-00904: "DTL"."TRANS_DATETIME": invalid identifier

Can anyone suggest how this join can be resolved?

Query:

SELECT *
FROM header  hdr
JOIN detail  dtl
    ON hdr.trans_number = ptd.trans_number
LEFT JOIN ( SELECT  product_code
                   ,cost_price
            FROM prodcost pr
            WHERE pr.last_amend_date = (SELECT MAX(pc.last_amend_date)
                                        FROM   prodcost pc
                                        WHERE  pc.product_code = pr.product_code
                                            AND pc.last_amend_date <= trunc(dtl.trans_datetime))) p
    ON ptd.product_code = p.product_code

Explanation I have header and detail tables that join on transaction number (this is actually irrelevant to the issue and can be considered a single table). I then need to left join the prodcost table on product_code. However, I have to restrict this based on the most recent last_amend_date prior to trans_datetime

So the detail table is a record of transactions with product IDs (product_code) and a transaction date (trans_datetime). The prodcost table has a record of the product costs with product IDs (product_code) and an effective date (last_amend_date). So a single product can have multiple costs depending on when the transaction took place. To identify the correct cost, I need to link on product_code and the most recent last_amend_date prior to trans_datetime.

I am aware I could split this into two queries and UNION them to provide the complete result set. However, I'd rather avoid that if at all possible. Any other suggestions on how to resolve would be appreciated.

Upvotes: 2

Views: 2690

Answers (1)

Boneist
Boneist

Reputation: 23578

Here's a couple of examples that ought to help you achieve what you're after (I made up my own tables/data since you declined to provide sample data, but the principle remains the same and you should be able to apply it to your own queries):

Example 1 (using left join and an analytic function):

with t1 as (select 1 id, to_date('01/01/2016', 'dd/mm/yyyy') dt, 'a' val from dual union all
            select 2 id, to_date('02/03/2016', 'dd/mm/yyyy') dt, 'b' val from dual union all
            select 3 id, to_date('03/02/2016', 'dd/mm/yyyy') dt, 'c' val from dual union all
            select 4 id, to_date('04/01/2016', 'dd/mm/yyyy') dt, 'd' val from dual),
     t2 as (select 1 id, 100 val, to_date('01/12/2015', 'dd/mm/yyyy') dt from dual union all
            select 1 id, 120 val, to_date('12/12/2015', 'dd/mm/yyyy') dt from dual union all
            select 1 id, 130 val, to_date('04/01/2016', 'dd/mm/yyyy') dt from dual union all
            select 2 id, 200 val, to_date('01/03/2016', 'dd/mm/yyyy') dt from dual union all
            select 3 id, 300 val, to_date('04/03/2016', 'dd/mm/yyyy') dt from dual union all
            select 3 id, 330 val, to_date('06/03/2016', 'dd/mm/yyyy') dt from dual)
-- end of mimicking two tables, t1 and t2, containing data. See SQL below:
select id,
       t1_dt,
       t1_val,
       t2_val
from   (select t1.id,
               t1.dt t1_dt,
               t1.val t1_val,
               t2.val t2_val,
               t2.dt t2_dt,
               row_number() over (partition by t1.id order by t2.dt desc) rn
        from   t1
               left outer join (select id,
                                       val,
                                       dt
                                from   t2) t2 on (t1.id = t2.id and t2.dt <= t1.dt))
where  rn = 1;

        ID T1_DT      T1_VAL     T2_VAL
---------- ---------- ------ ----------
         1 01/01/2016 a             120
         2 02/03/2016 b             200
         3 03/02/2016 c                
         4 04/01/2016 d

Example 2 (using a scalar subquery):

with t1 as (select 1 id, to_date('01/01/2016', 'dd/mm/yyyy') dt, 'a' val from dual union all
            select 2 id, to_date('02/03/2016', 'dd/mm/yyyy') dt, 'b' val from dual union all
            select 3 id, to_date('03/02/2016', 'dd/mm/yyyy') dt, 'c' val from dual union all
            select 4 id, to_date('04/01/2016', 'dd/mm/yyyy') dt, 'd' val from dual),
     t2 as (select 1 id, 100 val, to_date('01/12/2015', 'dd/mm/yyyy') dt from dual union all
            select 1 id, 120 val, to_date('12/12/2015', 'dd/mm/yyyy') dt from dual union all
            select 1 id, 130 val, to_date('04/01/2016', 'dd/mm/yyyy') dt from dual union all
            select 2 id, 200 val, to_date('01/03/2016', 'dd/mm/yyyy') dt from dual union all
            select 3 id, 300 val, to_date('04/03/2016', 'dd/mm/yyyy') dt from dual union all
            select 3 id, 330 val, to_date('06/03/2016', 'dd/mm/yyyy') dt from dual)
-- end of mimicking two tables, t1 and t2, containing data. See SQL below:
select id,
       dt t1_dt,
       val t1_val,
       (select max(val) keep (dense_rank first order by t2.dt desc) max_val
        from   t2
        where  t1.id = t2.id
        and    t2.dt <= t1.dt) t2_val
from   t1;

        ID T1_DT      T1_VAL     T2_VAL
---------- ---------- ------ ----------
         1 01/01/2016 a             120
         2 02/03/2016 b             200
         3 03/02/2016 c                
         4 04/01/2016 d

N.B. I'm assuming that t1.id is unique.

Upvotes: 1

Related Questions